https://develop.finki.ukim.mk/projects/FEIN/wiki/WikiStart
= FEiN =

== Краток опис
Веќе некое време управувам со моите **финансии** во //Google Sheets//, каде што со тек на време направив **//налик-апликација//**, со доста функции за анализа на тие податоци.

Начинот на кој управувам со мојот буџет и финансии е така што секојдневно **//внесувам трансакции//** кои со неколку __полиња__ ги **//полнат/празнат полињата//** кои покажуваат на која **//сметка(трансакциски, кредитни, кеш, девизни)** колку преостанало, колку е потрошено од истата и вкупно//. Исто го променуваат **//графикот на потрошувачка//** и //статистиките колку просечно сум истрошил во месецот, колку последните три дена и колку ми останува просечно да трошам на ден до крај на месецот//. Исто така имам и **//категории//** кои ги доделувам //рачно// на секоја трансакција кои подоцна полнат доделени нивни полиња, собирајќи и кажувајќи колку имам **//потрошено по категорија//**. Тие информации подоцна служат за **//креирање на графици за визуелна анализа на потрошеното//**.

Цел овој систем добро ме служи но станува малце заморно да се внесуваат трансакциите рачно, притоа запазувајќи го форматот кој сам сум си го креирал и пазење да се внесат таговите и датите без синтаксички грешки, кои во почетокот беа подложни на човечки грешки, но остануваат и до ден денес.

Со креирање на **овој систем** сакам да го **решам** првично тој **проблем**, __доведување на човечката грешка на минимум и намалување на времето за внесување на трансакциите__. Подоцна овој систем би овозможил полесно предавање на податоците во модел за машинско учење кој би предвидувал и проектирал информации околу личната потрошувачка...

Системот го замислувам во форма на респонзивна веб апликација(најчесто користена од мобилен телефон) која има централна датабаза до која пристапува секој индивидуален корисник преку неговата сметка.

**FEiN** е име кое на модерен начин го испишува изговорот на зборот Fin кој означува финансирање, но не обично, туку Fine-Tuned, кој збор исто така може да се испише на таков модерен начин. **FEiN** __исто така е и сленг збор во англискиот јазик кој означува голема желба/зависност. Така планирам мојот систем да стане дел од секојдневието, да створи зависност кај луѓето да си го менаџираат буџетот...__

== Изработува
||=Име и презиме=||=Индекс=||
||Василаки Тоцили||211101||
 Предмет::
     Бази на податоци во 2025/2026 зимски семестар[[BR]]
 Под менторство на::
     проф. д-р Вангел Ајановски

== Фази на изработка
||=Фаза=||=Име на фаза=||=Статус=||
||=Ф0=||[wiki:About Дефинирање на проектот]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф1=||[wiki:ERModel Концептуален дизајн - ЕР Дијаграм и податочни побарувања]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф2=||[wiki:RelationalDesign Логички и физички дизајн - Креирање база податоци (со SQL DDL)]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф3=||[wiki:UseCaseModel Апликативен дизајн - Случаи на употреба и сценарија за пристап до базата]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф4=||[wiki:PrototypeApplication Прототип апликација]||  [[span(style=color: green, Одобрена )]]  ||

||=Milestone 1=||Презентација на прототип ||  [[span(style=color: green, одржана)]]  ||

||=Ф5=||[wiki:Normalization Нормализација]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф6=||[wiki:AdvancedReports Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф7=||[wiki:AdvancedDatabaseDevelopment Напреден развој на датабаза (Тригери, погледи)]||  [[span(style=color: green, Одобрена)]]  ||
||=Ф8=||[wiki:AdvancedApplicationDevelopment Напреден апликативен развој (Трансакции, Pooling)]||  [[span(style=color: green, Одобрена)]]  ||
||=Ф9=||[wiki:OtherTopics Other topics (Performance, Security, ...)]||  [[span(style=color: blue, Чека на одобрување)]]  ||

||=Milestone 2=||Презентација на финалната верзија од проектот||  [[span(style=color: red, не е одржана)]]  ||

[wiki:Version1 Верзија 1]

https://develop.finki.ukim.mk/projects/FEIN/wiki/Version1
= FEiN =

== Краток опис
Веќе некое време управувам со моите **финансии** во //Google Sheets//, каде што со тек на време направив **//налик-апликација//**, со доста функции за анализа на тие податоци.

Начинот на кој управувам со мојот буџет и финансии е така што секојдневно **//внесувам трансакции//** кои со неколку __полиња__ ги **//полнат/празнат полињата//** кои покажуваат на која **//сметка(трансакциски, кредитни, кеш, девизни)** колку преостанало, колку е потрошено од истата и вкупно//. Исто го променуваат **//графикот на потрошувачка//** и //статистиките колку просечно сум истрошил во месецот, колку последните три дена и колку ми останува просечно да трошам на ден до крај на месецот//. Исто така имам и **//категории//** кои ги доделувам //рачно// на секоја трансакција кои подоцна полнат доделени нивни полиња, собирајќи и кажувајќи колку имам **//потрошено по категорија//**. Тие информации подоцна служат за **//креирање на графици за визуелна анализа на потрошеното//**.

Цел овој систем добро ме служи но станува малце заморно да се внесуваат трансакциите рачно, притоа запазувајќи го форматот кој сам сум си го креирал и пазење да се внесат таговите и датите без синтаксички грешки, кои во почетокот беа подложни на човечки грешки, но остануваат и до ден денес.

Со креирање на **овој систем** сакам да го **решам** првично тој **проблем**, __доведување на човечката грешка на минимум и намалување на времето за внесување на трансакциите__. Подоцна овој систем би овозможил полесно предавање на податоците во модел за машинско учење кој би предвидувал и проектирал информации околу личната потрошувачка...

Системот го замислувам во форма на мобилна апликација која има централна датабаза до која пристапува секој индивидуален корисник преку неговата сметка.

**FEiN** е име кое на модерен начин го испишува изговорот на зборот Fin кој означува финансирање, но не обично, туку Fine-Tuned, кој збор исто така може да се испише на таков модерен начин. **FEiN** __исто така е и сленг збор во англискиот јазик кој означува голема желба/зависност. Така планирам мојот систем да стане дел од секојдневието, да створи зависност кај луѓето да си го менаџираат буџетот...__

== Изработува
||=Име и презиме=||=Индекс=||
||Василаки Тоцили||211101||
 Предмет::
     Бази на податоци во 2024/2025 зимски семестар[[BR]]
 Под менторство на::
     проф. д-р Вангел Ајановски

== Фази на изработка
||=Фаза=||=Име на фаза=||=Статус=||
||=Ф0=||[wiki:About Дефинирање на проектот]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф1=||[wiki:ERModel Концептуален дизајн - ЕР Дијаграм и податочни побарувања]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф2=||[wiki:RelationalDesign Логички и физички дизајн - Креирање база податоци (со SQL DDL)]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф3=||[wiki:UseCaseModel Апликативен дизајн - Случаи на употреба и Сценарија за пристап до базата - SQL Погледи]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф4=||[wiki:AdvancedReports Напредни извештаи од базата (SQL и складирани процедури)]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф5=||[wiki:PrototypeApplication Прототип апликација - Основни функционалности]||  [[span(style=color: green, Одобрена )]]  ||
||=Ф6=||[wiki:Normalization Нормализација и подобрување на дизајнот]||  [[span(style=color: orange, Започната)]]  ||
||=Ф7=||[wiki:AditionalApplicationDevelopment Напреден апликативен развој]||  [[span(style=color: orange, Започната)]]  ||


https://develop.finki.ukim.mk/projects/FEIN/wiki/About
= FEiN =

== Членови на тимот

||=Име и презиме=||=Индекс=||
||  Василаки Тоцили   ||  211101  ||

== Краток опис на проектот

**FEiN** е респонзивна веб апликација(најчесто користена од мобилен телефон) која овозможува едноставно и ефективно **//управување со личните финансии//**.

Корисниците **//внесуваат дневни трансакции//**, кои автоматски се пресметуваат и //распределуваат по сметки (трансакциски, кредитни, кеш, девизни)//, **//давајќи увид во преостанатите средства и потрошеното//**. Секоја трансакција може да се //**категоризира**// за понапредна анализа, овозможувајќи **//графички приказ//** на трошењето по категории. Апликацијата **//генерира статистики//** за **//месечна потрошувачка, дневни трошоци, како и проекции за идно трошење**//.

**FEiN** користи **централна база** на податоци, овозможувајќи сигурност и конзистентност на податоците за сите корисници. Со **FEiN**, наместо со //Excel-базирано следење//, времето за внесување на податоци се намалува, а шансите за човечки грешки се сведуваат на минимум. Во иднина, **FEiN** би овозможил интеграција со модели за машинско учење за предвидување и оптимизирање на личниот буџет.

== Детален опис на проектот
* **За кого е наменет проектот односно кој според вашата замисла ќе биде одговорен да ја поседува базата на апликацијата и да ја обезбедува како сервис - дали е тоа фирма/институација/организација или е поединец и краток опис за нивните потреби?**
	Проектот е наменет за //индивидуални корисници// кои сакаат да управуваат со своите лични финансии на едноставен и интуитивен начин. Корисниците креираат //сметки// со кои пристапуваат до //централна база// на податоци преку апликацијата. Базата ќе биде администрирана централизирано од тимот што стои зад //FEiN//, со цел обезбедување на сигурност, конзистентност и лесен пристап од било кој уред.
* **Какви проблеми во фирмата или организацијата ќе решавате?**
    //FEiN// ги решава следните проблеми поврзани со управување на личните финансии:
    1. //Осигурување на точност при внесување податоци//: За разлика од //Excel//, каде што форматните грешки и пропусти се чести, //FEiN// ќе обезбеди //стандардизиран начин за внесување// на трансакции. Ова ќе ја намали можноста за грешки и ќе им олесни на корисниците во следењето на нивниот буџет.
    2. //Подобрено корисничко искуство//: Апликацијата ќе понуди интуитивен и лесен за користење интерфејс, кој ќе го замени комплексното и визуелно ограничено искуство на //Excel//.
    3. //Автоматски пресметки и визуализации//: //FEiN// автоматски ќе ги пресметува вкупните потрошени и преостанати средства, ќе ги категоризира трошоците и ќе прикажува визуелизации за полесна анализа на финансиските податоци.
    4. //Пристап до детални статистики без дополнителни трошоци//: //За разлика од други апликации//, //FEiN// ги нуди сите статистики бесплатно, овозможувајќи длабински увид во финансиските навики.
- **Какви видови корисници ќе има апликацијата - во смисла на типови вработени во фирмата/институцијата или клиенти?**
	//FEiN// е наменета исклучиво за //индивидуални корисници//. Во почетната фаза, сите корисници ќе имаат //бесплатен пристап// до апликацијата и сите нејзини функционалности. //Нема// да има различни типови на //кориснички профили// или //премиум функции// во првата верзија.
- **Ако има други слични проекти во фирмата или во околината, или веќе постојат готови решенија во светот, во што е различен вашиот предлог проект.**
	FEiN се издвојува по следното:
	1. //Над Excel-базираните системи//: Решавајќи ги проблемите со рачно внесување и форматни грешки, FEiN обезбедува структурирано и автоматизирано управување со личните финансии, во значително подобрено корисничко искуство.
	2. //Конкуренција со слични апликации//: За разлика од многу слични апликации кои нудат напредни статистики и прогнози како премиум функции, FEiN ги нуди истите //бесплатно// и со //отворен код// од самиот почеток.
- **Дали се работи за веб, мобилна и/или десктоп апликација?**
	FEiN е планирана како //респонзивна веб апликација(најчесто користена од мобилен телефон)//, со фокус на достапност и практичност за корисниците, кои можат лесно да внесуваат трансакции и да ги следат своите финансии од својот паметен телефон.

[wiki:AboutVersion1 Верзија 1]

https://develop.finki.ukim.mk/projects/FEIN/wiki/AboutVersion1
= FEiN =

== Членови на тимот

||=Име и презиме=||=Индекс=||
||  Василаки Тоцили   ||  211101  ||

== Краток опис на проектот

**FEiN** е мобилна апликација која овозможува едноставно и ефективно **//управување со личните финансии//**.

Корисниците **//внесуваат дневни трансакции//**, кои автоматски се пресметуваат и //распределуваат по сметки (трансакциски, кредитни, кеш, девизни)//, **//давајќи увид во преостанатите средства и потрошеното//**. Секоја трансакција може да се //**категоризира**// за понапредна анализа, овозможувајќи **//графички приказ//** на трошењето по категории. Апликацијата **//генерира статистики//** за **//месечна потрошувачка, дневни трошоци, како и проекции за идно трошење**//.

**FEiN** користи **централна база** на податоци, овозможувајќи сигурност и конзистентност на податоците за сите корисници. Со **FEiN**, наместо со //Excel-базирано следење//, времето за внесување на податоци се намалува, а шансите за човечки грешки се сведуваат на минимум. Во иднина, **FEiN** би овозможил интеграција со модели за машинско учење за предвидување и оптимизирање на личниот буџет.

== Детален опис на проектот
* **За кого е наменет проектот односно кој според вашата замисла ќе биде одговорен да ја поседува базата на апликацијата и да ја обезбедува како сервис - дали е тоа фирма/институација/организација или е поединец и краток опис за нивните потреби?**
	Проектот е наменет за //индивидуални корисници// кои сакаат да управуваат со своите лични финансии на едноставен и интуитивен начин. Корисниците креираат //сметки// со кои пристапуваат до //централна база// на податоци преку апликацијата. Базата ќе биде администрирана централизирано од тимот што стои зад //FEiN//, со цел обезбедување на сигурност, конзистентност и лесен пристап од било кој уред.
* **Какви проблеми во фирмата или организацијата ќе решавате?**
    //FEiN// ги решава следните проблеми поврзани со управување на личните финансии:
    1. //Осигурување на точност при внесување податоци//: За разлика од //Excel//, каде што форматните грешки и пропусти се чести, //FEiN// ќе обезбеди //стандардизиран начин за внесување// на трансакции. Ова ќе ја намали можноста за грешки и ќе им олесни на корисниците во следењето на нивниот буџет.
    2. //Подобрено корисничко искуство//: Апликацијата ќе понуди интуитивен и лесен за користење интерфејс, кој ќе го замени комплексното и визуелно ограничено искуство на //Excel//.
    3. //Автоматски пресметки и визуализации//: //FEiN// автоматски ќе ги пресметува вкупните потрошени и преостанати средства, ќе ги категоризира трошоците и ќе прикажува визуелизации за полесна анализа на финансиските податоци.
    4. //Пристап до детални статистики без дополнителни трошоци//: //За разлика од други апликации//, //FEiN// ги нуди сите статистики бесплатно, овозможувајќи длабински увид во финансиските навики.
- **Какви видови корисници ќе има апликацијата - во смисла на типови вработени во фирмата/институцијата или клиенти?**
	//FEiN// е наменета исклучиво за //индивидуални корисници//. Во почетната фаза, сите корисници ќе имаат //бесплатен пристап// до апликацијата и сите нејзини функционалности. //Нема// да има различни типови на //кориснички профили// или //премиум функции// во првата верзија.
- **Ако има други слични проекти во фирмата или во околината, или веќе постојат готови решенија во светот, во што е различен вашиот предлог проект.**
	FEiN се издвојува по следното:
	1. //Над Excel-базираните системи//: Решавајќи ги проблемите со рачно внесување и форматни грешки, FEiN обезбедува структурирано и автоматизирано управување со личните финансии, во значително подобрено корисничко искуство.
	2. //Конкуренција со слични апликации//: За разлика од многу слични апликации кои нудат напредни статистики и прогнози како премиум функции, FEiN ги нуди истите //бесплатно// и со //отворен код// од самиот почеток.
- **Дали се работи за веб, мобилна и/или десктоп апликација?**
	FEiN е планирана како //мобилна апликација//, со фокус на достапност и практичност за корисниците, кои можат лесно да внесуваат трансакции и да ги следат своите финансии од својот паметен телефон.

https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModel
= Верзија 3

== ER дијаграм
[[Image(ERDiagramVer3.png)]]

== Податочни побарувања

=== Ентитети
1. **User** - ентитет за корисникот кој се логира
	-       user_id - SERIAL (примарен клуч)
	- user_name - VARCHAR(30) (атрибут)
	-         email - VARCHAR(30) (атрибут)
	-   password - VARCHAR(30) (атрибут)
2. **Transaction Account** - секој корисник има една или повеќе трансакциски акаунти/сметки(кредитна картичка, дебитна картичка, кеш, девизи...)
	- transaction_account_id - SERIAL (примарен клуч)
	-                          user_id - INT (надворешен клуч)
	-              account_name - VARCHAR(35) (атрибут)
	-                         balance - DECIMAL(10, 2) (атрибут)
3. **Transaction Breakdown** - за секоја трансакција(акција на плаќање/добивање пари) да можe да менува различни акаунти(сте платиле пола со картица, пола со кеш) и секоја трансакција да биде посложена(ако платите вие за повеќе луѓе, и очекувате да бидете отплатени од нив) потребен е ваков механизам во вид на ентитет кој ќе го овозможи тоа
	- transaction_breakdown_id - SERIAL (примарен клуч) 
	-                    transaction_id - INT (надворешен клуч)
	-      transaction_account_id - INT (надворешен клуч)
	-                    spent_amount - DECIMAL(10, 2) (атрибут)
	-                  earned_amount - DECIMAL(10, 2) (атрибут)
4. **Transaction** - трансакција, акција на трансфер на пари. Атрибутите amount и net_amount се потребни во случајот за сложени трансакции, кога пример вие плаќате на работа за јадењето на сите колеги во апликација за достава на храна и потоа колегите си го исплаќаат својот дел дали преку кеш директно вам или преку електронски трансфер со е-банкарство, вкупниот износ на целата нарачка кој го плаќате ќе се запише во amount, а додека со тригери ќе се ажурира net_amount со збир на сите вредности од атрибутите на помошните трансакции(earned_amount+spent_amount). Тие ќе бидат истата вредност доколку вие со трансакцијата го плаќате целиот износ за вас или пак добивате пари кои не треба да ги вратите
	-      transaction_id - SERIAL (примарен клуч)
	-                   tag_id - INT (надворешен клуч)
	- transaction_name - VARCHAR(100) (атрибут)
        -                  date - TIMESTAMPZ (атрибут)
	-                 amount - DECIMAL(10, 2) (атрибут)
	-           net_amount - DECIMAL(10, 2) (атрибут)
5.  **Tag** - помошни групи(тагови) со кои може да се класифицираат трансакциите, а со тоа да се изведуваат корисни статистики
	-       tag_id - SERIAL (примарен клуч)
	- tag_name - VARCHAR(50) (атрибут)


== Релации
- **!TransactionHasTransactionBreakdown** - 1:N една трансакција може да има повеќе помошни трансакции
- **!TagAssignedToTransaction** - N:N повеќе тагови можат да се доделат на повеќе трансакции
- **!TransactionBreakdownModifiesTransactionAccount** - 1:N една сметка може да биде менувана од повеќе помошни трансакции
- **!UserHasTransactionAccount** - 1:N еден корисник може да има повеќе сметки

== Историјат
[wiki:ERModelVer1 Верзија 1] \\
[wiki:ERModelVer2 Верзија 2] \\
[wiki:ERModelVer3 Верзија 3]


https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModelVer2
= Верзија 2

== ER дијаграм
[[Image(wiki:ERModel:ERDiagramVer2.png)]]

== Податочни побарувања

=== Ентитети
1. **User** - ентитет за корисникот кој се логира
	-       user_id - SERIAL (примарен клуч)
	- user_name - VARCHAR(30) (атрибут)
	-         email - VARCHAR(30) (атрибут)
	-   password - VARCHAR(30) (атрибут)
2. **Transaction Account**
	- transaction_account_id - SERIAL (примарен клуч)
	-                          user_id - INT (надворешен клуч)
	-              account_name - VARCHAR(35) (атрибут)
	-                         balance - DECIMAL(10, 2) (атрибут)
3. **Transaction Breakdown**
	- transaction_breakdown_id - SERIAL (примарен клуч) 
	-                    transaction_id - INT (надворешен клуч)
	-      transaction_account_id - INT (надворешен клуч)
	-                    spent_amount - DECIMAL(10, 2) (атрибут)
	-                  earned_amount - DECIMAL(10, 2) (атрибут)
4. **Transaction**
	-      transaction_id - SERIAL (примарен клуч)
	-                   tag_id - INT (надворешен клуч)
	- transaction_name - VARCHAR(100) (атрибут)
        -                  date - DATE (атрибут)
	-                 amount - DECIMAL(10, 2) (атрибут)
	-           net_amount - DECIMAL(10, 2) (атрибут)
5.  **Tag**
	-       tag_id - SERIAL (примарен клуч)
	- tag_name - VARCHAR(50) (атрибут)


== Релации
- **!TransactionHasTransactionBreakdown** - 1:N една трансакција може да има повеќе помошни трансакции
- **!TagAssignedToTransaction** - N:N повеќе тагови можат да се доделат на повеќе трансакции
- **!TransactionBreakdownModifiesTransactionAccount** - 1:1 една помошна трансакција менува една сметка
- **!UserHasTransactionAccount** - 1:N еден корисник може да има повеќе сметки

== Историјат
[wiki:ERModelVer1 Верзија 1] \\
[wiki:ERModelVer2 Верзија 2]

https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModelVer1
= Верзија 1

== ER дијаграм
[[Image(wiki:ERModel:ERDiagramVer1.png)]]

== Податочни побарувања

=== Ентитети
1. **User** - ентитет за корисникот кој се логира
	-       user_id - SERIAL (примарен клуч)
	- user_name - VARCHAR(30) (атрибут)
	-         email - VARCHAR(30) (атрибут)
	-   password - VARCHAR(30) (атрибут)
2. **Transaction Account**
	- transaction_account_id - SERIAL (примарен клуч)
	-                          user_id - INT (надворешен клуч)
	-              account_name - VARCHAR(35) (атрибут)
	-                         balance - DECIMAL(10, 2) (атрибут)
3. **Transaction Breakdown**
	- transaction_breakdown_id - SERIAL (примарен клуч) 
	-                    transaction_id - INT (надворешен клуч)
	-      transaction_account_id - INT (надворешен клуч)
	-                    spent_amount - DECIMAL(10, 2) (атрибут)
	-                  earned_amount - DECIMAL(10, 2) (атрибут)
4. **Transaction**
	-      transaction_id - SERIAL (примарен клуч)
	-                  day_id - INT (надворешен клуч)
	-                   tag_id - INT (надворешен клуч)
	- transaction_name - VARCHAR(100) (атрибут)
	-                 amount - DECIMAL(10, 2) (атрибут)
	-           net_amount - DECIMAL(10, 2) (атрибут)
5.  **Tag**
	-       tag_id - SERIAL (примарен клуч)
	- tag_name - VARCHAR(50) (атрибут)
6. **Day**
	-           day_id - SERIAL (примарен клуч)
	-      month_id - INT (надворешен клуч)
	-     day_name - VARCHAR(9) (атрибут)
	- day_number - INT (атрибут)
7. **Month**
	-       month_id - SERIAL (примарен клуч)
	-           year_id - INT (надворешен клуч)
	- month_name - VARCHAR(20) (атрибут)
8. **Year**
	-       year_id - SERIAL (примарен клуч)
	- year_name - INT (атрибут)

== Релации
- **has** - 1:N една година има повеќе месеци
- **has** - 1:N еден месец има повеќе денови
- **has** - 1:N еден ден може да има повеќе трансакции
- **has** - 1:N една трансакција може да има повеќе помошни трансакции
- **assigned** - N:N повеќе тагови можат да се доделат на повеќе трансакции
- **modifies** - 1:1 една помошна трансакција менува една сметка
- **has** - 1:N еден корисник може да има повеќе сметки

== Историјат
[wiki:ERModelVer1 Верзија 1]

https://develop.finki.ukim.mk/projects/FEIN/wiki/RelationalDesign
= Логички и физички дизајн

== Релациска шема (со мапирачка трансформација)
=== Ознаки
- примарни клучеви     - __**болдирани и подвлечени__**
- not null атрибути        - **болдирани**
- надворешни клучеви - * до името на надворешниот клуч и во заграда табелата кон која референцира
- останати атрибути      - без ознака
=== Табели
- USER (**__user_id__**, **user_name**, **email**, **password**)
- TRANSACTION_ACCOUNT (__**transaction_account_id**__, account_name, balance, user_id* (USER))
- TRANSACTION (__**transaction_id__**, transaction_name, **amount**, net_amount, **date**)
- TRANSACTION_BREAKDOWN (__**transaction_breakdown_id__**, transaction_id* (TRANSACTION), transaction_account_id* (TRANSACTION_ACCOUNT), spent_amount, earned_amount)
- TAG (__**tag_id__**, **tag_name**)
- TAG_ASSIGNED_TO_TRANSACTION(__**tag_assigned_to_transaction_id**__, **tag_id*** (TAG), **transaction_id*** (TRANSACTION))

=== DDL скрипта за креирање и бришење на табелите
[wiki:ddlScript.sql DDL скрипта]
=== DML скрипта за полнење на табелите со податоци
[wiki:dmlScript.sql DML скрипта]
=== Релациски дијаграм изваден од DBeaver
[[Image(DBeaverExportVer2.png)]]

https://develop.finki.ukim.mk/projects/FEIN/wiki/ddlScript.sql
{{{#!sql
-- Delete tables if they exist
DROP TABLE IF EXISTS transaction_breakdown CASCADE;
DROP TABLE IF EXISTS transaction CASCADE;
DROP TABLE IF EXISTS transaction_account CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS tag_assigned_to_transaction CASCADE;

-- Create USER table
CREATE TABLE "user" (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL
);

-- Create TRANSACTION_ACCOUNT table
CREATE TABLE transaction_account (
    transaction_account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(50),
    balance DECIMAL(10, 2),
    user_id INT REFERENCES "user"(user_id)
);

-- Create TRANSACTION table
CREATE TABLE transaction (
    transaction_id SERIAL PRIMARY KEY,
    transaction_name VARCHAR(100),
    amount DECIMAL(10, 2) NOT NULL,
    net_amount DECIMAL(10, 2),
    date TIMESTAMPTZ NOT NULL
);

-- Create TAG table
CREATE TABLE tag (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL
);

-- Create TAG_ASSIGNED_TO_TRANSACTION table
CREATE TABLE tag_assigned_to_transaction (
    tag_assigned_to_transaction_id SERIAL PRIMARY KEY,
    transaction_id INT NOT NULL REFERENCES transaction(transaction_id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE
);

-- Create TRANSACTION_BREAKDOWN table
CREATE TABLE transaction_breakdown (
    transaction_breakdown_id SERIAL PRIMARY KEY,
    transaction_id INT REFERENCES transaction(transaction_id),
    transaction_account_id INT REFERENCES transaction_account(transaction_account_id),
    spent_amount DECIMAL(10, 2),
    earned_amount DECIMAL(10, 2)
);
}}}

https://develop.finki.ukim.mk/projects/FEIN/wiki/dmlScript.sql
{{{#!sql
INSERT INTO "user" (user_name, email, password) VALUES
('John Doe', 'john@example.com', 'password123'),
('Jane Smith', 'jane@example.com', 'securepass'),
('Alice Johnson', 'alice@example.com', 'alicepwd');

INSERT INTO tag (tag_name) VALUES
('Food'),
('Work'),
('Groceries'),
('Entertainment'),
('Bills');

INSERT INTO transaction_account (account_name, balance, user_id) VALUES
('Cash', 5000.00, 1),
('Bank Account', 20000.00, 1),
('Credit Card', -1000.00, 2),
('Savings', 15000.00, 2),
('Foreign Currency', 300.00, 3);

INSERT INTO transaction (transaction_name, amount, net_amount, date) VALUES 
('Bought groceries', 1000.00, 800.00, '2024-11-23 13:51:35+02:00'),
('Paid rent', 15000.00, 15000.00, '2024-11-01 17:01:41+02:00'),
('Dinner with friends', 2500.00, 1500.00, '2024-11-29 22:02:22+02:00'),
('Movie tickets', 600.00, 600.00, '2024-11-30 17:38:00+02:00'),
('Electricity bill', 1200.00, 1200.00, '2024-11-02 07:03:35+02:00');

INSERT INTO tag_assigned_to_transaction (transaction_id, tag_id) VALUES 
(1, 1),
(2, 5),
(3, 2),
(4, 4),
(5, 5);

INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, spent_amount, earned_amount) VALUES
(1, 1, 1000.00, 200.00),  -- Spent 1000 on Cash, earned back 200
(1, 2, 0.00, 800.00),     -- Earned back 800 on Bank Account
(2, 2, 15000.00, 0.00),   -- Spent 15000 on Bank Account for rent
(3, 1, 2500.00, 1000.00), -- Spent 2500 on Cash, earned back 1000
(4, 3, 600.00, 0.00);     -- Spent 600 on Credit Card for movie
}}}

https://develop.finki.ukim.mk/projects/FEIN/wiki/UseCaseModel
== Апликативен дизајн - Случаи на употреба и Сценарија за пристап до базата - SQL Погледи

== Актери
**Ненајавен корисник**
- Не може да управува со апликацијата пред да се најави бидејќи нема да има база на податоци со која може да управува
**Најавен корисник**
- Корисникот креира сметка за пристап до апликацијата за да управува со личните финансии. Може да:
 - Додава трансакциски сметки (на пр. готовина, кредитна картичка, заштеда)
 - Внесува дневни трансакции со тагови, имиња, износи и датуми
 - Прегледува извештаи за месечни/годишни трошоци и распоред на дневни трансакции

== Сценарија

- [wiki:RegistracijaKorisnik Регистрација на корисник]
- [wiki:NajavaKorisnik Најава на корисник]
- [wiki:DodavanjeTransakciska Додавање на трансакциска сметка]
- [wiki:PregledSmetki Преглед на сметки]
- [wiki:DodavanjeTransakcija Додавање на трансакција]
- [wiki:PregledNaTransakcii Преглед на трансакции]
- [wiki:AzuriranjeBrisenjeTransakcija Ажурирање/бришење трансакции]
- [wiki:Izvestai Извештаи (годишни/месечни/по тагови/графици)]

== Најважни случаи на употреба

- [wiki:RegistracijaKorisnik Регистрација на корисник]
- [wiki:DodavanjeTransakciska Додавање на трансакциска сметка]
- [wiki:DodavanjeTransakcija Додавање на трансакција]
- [wiki:AzuriranjeBrisenjeTransakcija Ажурирање/бришење трансакции]
- [wiki:Izvestai Извештаи (годишни/месечни/по тагови/графици)]

https://develop.finki.ukim.mk/projects/FEIN/wiki/RegistracijaKorisnik
== Регистрација на корисник

==== Актери: **Ненајавен корисник**

1. Корисникот притиска на копчето за регистрација
2. Се појавува форма за регистрација
3. Корисникот ги внесува своето име, емаил и лозинка во апликацијата.
4. Апликацијата внесува нов запис во табелата {{{user}}}
{{{#!sql
INSERT INTO user (user_name, email, password)
VALUES ('Јован Петров', 'jovan.petrovski@example.com', 'hashed_password');
}}}
5. По успешно креирање на сметка тој може да се најави на системот

https://develop.finki.ukim.mk/projects/FEIN/wiki/NajavaKorisnik
== Најава на корисник

==== Актери: **Ненајавен корисник**

1. Корисникот притиска на копчето за најава
2. Се појавува форма за најава
3. Корисникот ги внесува своите емаил и лозинка во полето за најава
4. Апликацијата проверува дали емаилот и лозинката постојат во табелата {{{user}}}
{{{#!sql
SELECT user_id 
FROM user 
WHERE email = 'jovan.petrovski@example.com' AND password = 'hashed_password';
}}}
5. Ако внесените податоци се точни:
 - Корисникот е пренасочен кон почетната страница на апликацијата и добива пристап до функционалностите
===== Алтернативен тек:
6. Ако внесените податоци не се точни:
 - Апликацијата прикажува порака за грешка: „Невалидно корисничко име или лозинка“


https://develop.finki.ukim.mk/projects/FEIN/wiki/DodavanjeTransakciska
== Додавање на трансакциска сметка

==== Актери: **Најавен корисник**

1. Корисникот притиска на копчето „Додај сметка“
2. Се појавува форма за внес на информации за трансакциската сметка
3. Корисникот ги внесува следниве податоци:
 - Име на сметка (на пр. „Трансакциска сметка НЛБ“)
 - Почетен баланс (на пр. „50000 МКД“)
4. Апликацијата внесува нов запис во табелата {{{transaction_account}}} поврзан со тековниот корисник.
{{{#!sql
INSERT INTO transaction_account (account_name, balance, user_id)
VALUES ('Трансакциска сметка НЛБ', 50000, 101);
}}}
5. По успешно додавање на сметката, корисникот добива известување:
 - „Сметката е успешно додадена!“
===== Алтернативен тек
6. Корисникот не ги внел сите потребни податоци (на пр. име на сметката е празно):
 - Апликацијата прикажува порака: „Ве молиме внесете валидни податоци за сметката“

https://develop.finki.ukim.mk/projects/FEIN/wiki/PregledSmetki
== Преглед на сметки

==== Актери: **Најавен корисник**

Откако ќе се додаде сметката, корисникот може да ги види сите свои сметки на следниов начин:
1. Корисникот притиска на табот „Мои сметки“ од менито во апликацијата.
2. Апликацијата испраќа SQL прашање до базата за да ги пронајде сите трансакциски сметки поврзани со корисникот
{{{#!sql
SELECT account_name, balance 
FROM transaction_account 
WHERE user_id = 101;
}}}
3. Апликацијата ги добива податоците од базата и ги прикажува во листа, со следниве информации за секоја сметка:
 - Име на сметката
 - Тековен баланс
4. Корисникот може да избере една од сметките за дополнителни опции:
 - Преглед на трансакции
 - Уредување на сметката
 - Бришење на сметката

===== Алтернативен тек
5. Корисникот нема додадено сметки, апликацијата прикажува порака:
 - „Немате додадено трансакциски сметки. Притиснете на копчето ‘Додај сметка’ за да започнете“

https://develop.finki.ukim.mk/projects/FEIN/wiki/DodavanjeTransakcija
== Додавање трансакција

==== Актери: **Најавен корисник**
1. Корисникот притиска на копчето за додавање на трансакции
2. Се отвора форма каде корисникот ги внесува следните детали за трансакцијата:
 - Дали трансакцијата додава или одзема од сметка (задолжително поле)
 - Име на трансакција (задолжително поле)
 - Износ на цела трансакција (задолжително поле)
 - Датум на трансакцијата (опционално, доколку не се внесе се зема моменталното време)
 - Таг за категоризација (опционално, но препорачливо)
 - Сметка на која трансакцијата се поврзува (се бира од достапните)
3. Корисникот може да додаде дополнителни детали преку динамично додавање на полиња со клик на копче за додавање кои ќе бидат зачувани како {{{earned_amount}}} и {{{spent_amount}}} во табелата {{{transaction_breakdown}}}
4. Апликацијата ги валидира внесените податоци (на пример, проверува дали сите задолжителни полиња се пополнети и дали износот е валиден број)
5. Апликацијата внесува нов запис во табелите {{{transaction}}} и {{{tag_assigned_to_transaction}}}:
{{{#!sql
INSERT INTO transaction (transaction_name, amount, net_amount, date)
VALUES ('Пазарување', 3500.00, 0, '2024-11-23 13:51:35+02:00');
RETURNING transaction_id;
}}}
{{{#!sql
INSERT INTO tag_assigned_to_transaction (transaction_id, tag_id)
VALUES (1, 3);
RETURNING tag_assigned_to_transaction_id;
}}}
6. Трансакцијата тригерира креирање запис во {{{transaction_breakdown}}} кој исто така ја ажурира сметката и пресметува {{{net_amount}}} преку тригер:
- Во зависност од тоа дали трансакцијата додава или одзема се прави додавање во соодветното поле на {{{transaction_breakdown}}}:
 - За додавање на сметка:
{{{#!sql
INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount)
VALUES (1, 2, 3500, 0);
}}}
 - За одземање од сметка:
{{{#!sql
INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount)
VALUES (1, 2, 0, 3500);
}}}
 - На крај се ажурира балансот во {{{transaction_account}}}
{{{#!sql
UPDATE transaction_account:
SET balance = balance - 3500
WHERE transaction_account_id = 2;
}}}
7. Ако корисникот додал дополнителни детали преку динамично додавање на полиња(на пример, {{{earned_amount}}} и {{{spent_amount}}}), апликацијата внесува соодветни записи во табелата {{{transaction_breakdown}}}:
{{{#!sql
INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount)
VALUES (1, 2, 0, 1500),
       (1, 3, 200, 0);
}}}
8. Тригер во базата:
 - Автоматски ја пресметува и ажурира вредноста на {{{net_amount}}} во табелата {{{transaction}}} според деталите внесени во табелата {{{transaction_breakdown}}}
9. По успешното додавање, тригерот:
 - Го ажурира балансот на поврзаната трансакциска сметка во табелата {{{transaction_account}}}:
{{{#!sql
UPDATE transaction_account
SET balance = 
    CASE 
        WHEN transaction_account_id = 2 THEN balance - 1500
        WHEN transaction_account_id = 3 THEN balance + 200
        ELSE balance
    END
WHERE transaction_account_id IN (2, 3);
}}}
 - Го известува корисникот за успешното додавање на трансакцијата.

https://develop.finki.ukim.mk/projects/FEIN/wiki/PregledNaTransakcii
== Преглед на сите трансакции

==== Актери: **Најавен корисник**
1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата
2. Апликацијата испраќа SQL прашање до базата за да ги пронајде сите трансакции поврзани со корисникот, хронолошки подредени
{{{#!sql
SELECT 
    t.transaction_id, 
    t.transaction_name, 
    t.amount, 
    t.net_amount, 
    t.date, 
    STRING_AGG(DISTINCT tg.tag_name, ', ') AS tag_names, -- Aggregate multiple tag names
    ta.account_name
FROM 
    public."transaction" t
JOIN 
    public.transaction_breakdown tb ON t.transaction_id = tb.transaction_id
JOIN 
    public.transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
LEFT JOIN 
    public.tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
LEFT JOIN 
    public.tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101
GROUP BY 
    t.transaction_id, t.transaction_name, t.amount, t.net_amount, t.date, ta.account_name
ORDER BY 
    t.date DESC;
}}}
3. Апликацијата ги прикажува трансакциите како листа, со следниве информации за секоја трансакција:
 - ID на трансакција
 - Име на трансакција
 - Износ (amount)
 - Нето износ (net_amount)
 - Датум на трансакцијата
 - Таг/ови (ако постојат)
 - Сметка на која е поврзана трансакцијата
4. До секоја трансакција, апликацијата прикажува две копчиња:
 - „Ажурирај“
 - „Избриши“

http://develop.finki.ukim.mk/projects/FEIN/wiki/AzuriranjeBrisenjeTransakcija
== Ажурирање/бришење на трансакции

==== Актери: **Најавен корисник**

=== Сценарио за ажурирање на трансакција
1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата
2. Корисникот притиска на копчето „Ажурирај“ од одредена трансакција
3. Се отвора форма со веќе пополнети податоци за избраната трансакција
4. Корисникот ги менува податоците (на пр. износ, датум, тагови, или сметка)
5. Апликацијата ги валидира внесените податоци и ги ажурира соодветните записи во базата:
{{{#!sql
UPDATE transaction
SET transaction_name = 'Променето име',
    amount = 4000,
    date = '2024-11-24 10:30:00+02:00',
WHERE transaction_id = 1;
}}}
6. Ако има промена на тагови, претходно внесените записи се бришат и се додаваат новите тагови:
{{{#!sql
DELETE FROM public.tag_assigned_to_transaction
WHERE transaction_id = 1;

INSERT INTO public.tag_assigned_to_transaction (transaction_id, tag_id)
VALUES 
    (1, 2),   -- Пример за ново додаден таг
    (1, 3);   -- Пример за друг нов таг
}}}
7. Ако е направена промена во {{{amount}}}:
 - Ажурирањето тригерира промена на записите во {{{transaction_breakdown}}} и {{{transaction_account}}} преку тригер
8. По успешно ажурирање, корисникот добива порака:
 - „Трансакцијата е успешно ажурирана!“

=== Сценарио за бришење на трансакција
1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата
2. Корисникот притиска на копчето „Избриши“ од одредена трансакција
3. Апликацијата прикажува порака за потврда:
 - „Дали сте сигурни дека сакате да ја избришете оваа трансакција?“
4. Корисникот потврдува
5. Апликацијата ги брише сите записи поврзани со трансакцијата:
 - Бришење од {{{transaction_breakdown}}}:
{{{#!sql
DELETE FROM transaction_breakdown
WHERE transaction_id = 1;
}}}
 - Бришење од {{{tag_assigned_to_transaction}}}:
{{{#!sql
DELETE FROM public.tag_assigned_to_transaction
WHERE transaction_id = 1;
}}}
 - Бришење од {{{transaction}}}:
{{{#!sql
DELETE FROM transaction
WHERE transaction_id = 1;
}}}
6. Тригер автоматски го ажурира балансот на соодветната сметка во {{{transaction_account}}}
7. Корисникот добива порака:
 - „Трансакцијата е успешно избришана!“

https://develop.finki.ukim.mk/projects/FEIN/wiki/Izvestai
== Извештаи

==== Актери: Најавен корисник
1. Корисникот пристапува до табот "Извештаи" од главното мени на апликацијата
2. Се прикажува интерактивна страница со опции за избор на типот на извештај:
 - Годишен извештај
 - Месечен извештај
 - Извештај по тагови
 - Извештај по сметки
 - Трендови на потрошувачка
3. Корисникот избира тип на извештај и според тоа се пренасочува кон страница за тој извештај

===== Годишен извештај
- Корисникот избира "Годишен извештај" и потоа одбира година од dropdown мени
- Апликацијата прикажува пита дијаграм со распределба на трошоците по тагови за избраната година
- Дополнително, се прикажува график на времеплов (line chart) кој ги покажува вкупните месечни трошоци за секој месец од годината

===== Месечен извештај
- Корисникот избира "Месечен извештај" и потоа одбира месец и година од два dropdown менија
- Апликацијата прикажува пита дијаграм со трошоците по тагови за избраниот месец
- Дополнително, се прикажува график на времеплов (line chart) кој ги прикажува дневните трошоци во месецот

===== Извештај по тагови
- Корисникот избира "Извештај по тагови" и одбира еден или повеќе тагови од листа
- Апликацијата прикажува пита дијаграм кој ги содржи сите избрани тагови
- Под графикот се покажуваат вкупните трошоци за секој избран таг, подредени хронолошки

===== Извештај по сметки
- Корисникот избира "Извештај по сметки" и одбира сметка од листата
- Апликацијата прикажува график на времеплов (line chart) сите приходи и расходи за избраната сметка
- Апликацијата прикажува листа која ги покажува сите приходи и расходи за избраната сметка под графикот

===== Трендови на потрошувачка
- Корисникот избира "Трендови на потрошувачка" и времетраење (година, месец, или недела)
- Апликацијата прикажува график кој ги споредува трендовите на трошоците за секој период (на пр. потрошувачка за јануари 2023 наспроти јануари 2024)

https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReports
= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)

- [wiki:AdvancedReport1 Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки]
- [wiki:AdvancedReport2 Надминување на баланс на една сметка со трансакција во сегашно време]
- [wiki:AdvancedReport3 Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки]
- [wiki:AdvancedReport4 Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време]
- [wiki:AdvancedReport5 Сумарни приходи и расходи по месеци]
- [wiki:AdvancedReport6 Број на трансакции што ги надминуваат приходите на сметка]
- [wiki:AdvancedReport7 Сумарни податоци за тагови]
- [wiki:AdvancedReport8 Просечно трошење во последните 3 дена за цел систем]
- [wiki:AdvancedReport9 Просечно трошење во последните 3 дена за корисник]
- [wiki:AdvancedReport25 Просечно трошење во последните N дена]
- [wiki:AdvancedReport10 Вкупно потрошено во тековниот месец за цел систем]
- [wiki:AdvancedReport11 Вкупно потрошено во тековниот месец за корисник]
- [wiki:AdvancedReport12 Дневен буџет до крајот на месецот за корисник]
- [wiki:AdvancedReport13 Долг на кредитна картичка од минатиот месец за цел систем]
- [wiki:AdvancedReport14 Долг на кредитна картичка од минатиот месец за корисник]
- [wiki:AdvancedReport15 Трендови на трошење според тагови за цел систем]
- [wiki:AdvancedReport16 Трендови на трошење според тагови за корисник]
- [wiki:AdvancedReport17 Вкупно трошење според тагови]
- [wiki:AdvancedReport18 Сметки со највисоко вкупно трошење во изминатата година]
- [wiki:AdvancedReport19 Најчести времиња за трансакции]
- [wiki:AdvancedReport20 Трансакции според тагови со највисоко трошење за цел систем]
- [wiki:AdvancedReport21 Трансакции според тагови со највисоко трошење за корисник]
- [wiki:AdvancedReport22 Годишни трендови на трансакции за цел систем]
- [wiki:AdvancedReport23 Годишни трендови на трансакции за корисник]
- [wiki:AdvancedReport24 Неактивни тагови]

== 

[wiki:AdvancedReportsVersion1 Верзија 1]
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport1
==== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
{{{#!sql
CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
    p_account_name TEXT
)
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    account_name TEXT,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP,
    calculated_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_balances AS (
        SELECT
            u.user_id,
            u.user_name,
            ta.account_name,
            t.transaction_id,
            t.transaction_name,
            tb.spent_amount AS transaction_amount,
            t.date AS transaction_date,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
                AS calculated_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t ON tb.transaction_id = t.transaction_id
        WHERE ta.account_name = p_account_name
    )
    SELECT *
    FROM cumulative_balances
    WHERE transaction_amount > calculated_balance
      AND transaction_amount > 0
    ORDER BY transaction_date DESC;
END;
$$;

}}}

==== Релациона алгебра
- U(user_id, user_name)
- TA(transaction_account_id, user_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, transaction_name, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтер според име на акаунт:
- F1 ← σ,,account_name = p_account_name,,(J3)

Пресметка на кумулативен баланс:
- CB ← γ,,user_id, user_name, account_name, transaction_id, transaction_name, transaction_amount, transaction_date; Σ(transaction_amount) OVER (PARTITION BY transaction_account_id ORDER BY transaction_date) → calculated_balance,,(F1)

каде `transaction_amount = spent_amount` и кумулативната сума е `Σ(earned_amount - spent_amount)`

Филтрирање на трансакциите кои го надминуваат балансот:
- R ← σ,,transaction_amount > calculated_balance ∧ transaction_amount > 0,,(CB)

Подредување хронолошки(почнувајќи од најновата трансакција до најстарата):
- R_final ← τ,,transaction_date DESC,,(R)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport2
==== Надминување на баланс на една сметка со трансакција во сегашно време
{{{#!sql
CREATE OR REPLACE FUNCTION get_current_account_overdrafts()
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    account_name TEXT,
    current_balance NUMERIC,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.user_id,
        u.user_name,
        ta.account_name,
        ta.balance,
        t.transaction_id,
        t.transaction_name,
        tb.spent_amount,
        t.date
    FROM transaction_account ta
    JOIN "user" u ON ta.user_id = u.user_id
    JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN transaction t ON tb.transaction_id = t.transaction_id
    WHERE tb.spent_amount > ta.balance
      AND tb.spent_amount > 0
    ORDER BY t.date DESC;
END;
$$;
}}}

==== Релациона алгебра
- U(user_id, user_name)
- TA(transaction_account_id, user_id, account_name, balance)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, transaction_name, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање на трансакции што го надминуваат тековниот баланс:
- R ← σ,,spent_amount > balance ∧ spent_amount > 0,,(J3)

Подредување хронолошки (од најнова кон најстара трансакција):
- R_final ← τ,,date DESC,,(R)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport3
==== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
{{{#!sql
CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance()
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP,
    calculated_total_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_totals AS (
        SELECT
            u.user_id,
            u.user_name,
            t.transaction_id,
            t.transaction_name,
            tb.spent_amount AS transaction_amount,
            t.date AS transaction_date,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY u.user_id ORDER BY t.date)
                AS calculated_total_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t ON tb.transaction_id = t.transaction_id
    )
    SELECT *
    FROM cumulative_totals
    WHERE transaction_amount > calculated_total_balance
      AND transaction_amount > 0
    ORDER BY user_id, transaction_date DESC;
END;
$$;
}}}

==== Релациона алгебра
- U(user_id, user_name)
- TA(transaction_account_id, user_id)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, transaction_name, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T

Пресметка на вкупен кумулативен баланс по корисник:
- CT ← γ,,user_id, user_name, transaction_id, transaction_name, transaction_amount, transaction_date; Σ(transaction_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) → calculated_total_balance,,(J3)

каде `transaction_amount = spent_amount` и кумулативната сума е  
`Σ(earned_amount - spent_amount)` за сите сметки на корисникот

Филтрирање на трансакции кои го надминуваат вкупниот баланс:
- R ← σ,,transaction_amount > calculated_total_balance ∧ transaction_amount > 0,,(CT)

Подредување хронолошки (по корисник, од најнова кон најстара трансакција):
- R_final ← τ,,user_id, transaction_date DESC,,(R)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport4
==== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува вкупна вредност
{{{#!sql
CREATE OR REPLACE FUNCTION get_current_user_total_balance_overdrafts()
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    transaction_id INT,
    transaction_amount NUMERIC,
    user_total_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH total_user_balance AS (
        SELECT
            u.user_id,
            SUM(ta.balance) AS total_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        GROUP BY u.user_id
    )
    SELECT
        u.user_id,
        u.user_name,
        t.transaction_id,
        tb.spent_amount AS transaction_amount,
        tub.total_balance AS user_total_balance
    FROM transaction_account ta
    JOIN "user" u ON ta.user_id = u.user_id
    JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN transaction t ON tb.transaction_id = t.transaction_id
    JOIN total_user_balance tub ON u.user_id = tub.user_id
    WHERE
        tb.spent_amount > tub.total_balance
        AND tb.spent_amount > 0
        AND t.date <= CURRENT_DATE;
END;
$$;
}}}

==== Релациона алгебра
- U(user_id, user_name)
- TA(transaction_account_id, user_id, balance)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN за пресметка на вкупен моментален баланс по корисник:
- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
- TBAL ← γ,,user_id; Σ(balance) → total_balance,,(J1)

JOIN на сите табели за трансакции:
- J2 ← TA ⨝,,TA.user_id = U.user_id,, U
- J3 ← J2 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J4 ← J3 ⨝,,TB.transaction_id = T.transaction_id,, T
- J5 ← J4 ⨝,,U.user_id = TBAL.user_id,, TBAL

Филтрирање на трансакции кои го надминуваат ВКУПНИОТ моментален баланс:
- R ← σ,,spent_amount > total_balance ∧ spent_amount > 0 ∧ date ≤ CURRENT_DATE,,(J5)

Подредување по корисник:
- R_final ← τ,,user_id,,(R)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport5
==== Сумарни приходи и расходи по месеци
Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
{{{#!sql
CREATE OR REPLACE FUNCTION get_monthly_system_balance()
RETURNS TABLE (
    month TEXT,
    total_income NUMERIC,
    total_expense NUMERIC,
    net_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        TO_CHAR(t.date, 'YYYY-MM'),
        SUM(tb.earned_amount),
        SUM(tb.spent_amount),
        SUM(tb.earned_amount) - SUM(tb.spent_amount)
    FROM transaction t
    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    GROUP BY TO_CHAR(t.date, 'YYYY-MM')
    ORDER BY month;
END;
$$;
}}}
Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците

==== Релациона алгебра
- T(transaction_id, date)
- TB(transaction_id, earned_amount, spent_amount)

JOIN на табелите:
- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB

Групирање по месец и пресметка на приходи и расходи:
- M ← γ,,month = FORMAT(date, 'YYYY-MM'); Σ(earned_amount) → total_income, Σ(spent_amount) → total_expense,,(J1)

Пресметка на нето состојба:
- R ← π,,month, total_income, total_expense, (total_income − total_expense) → net_balance,,(M)

Подредување по месец:
- R_final ← τ,,month,,(R)

> За само проблематични месеци, се додава уште еден чекор:

Филтрирање на проблематични месеци (негативна состојба):
- P ← σ,,net_balance < 0,,(R_final)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport6
==== Број на трансакции што ги надминуваат приходите на сметка
{{{#!sql
CREATE OR REPLACE FUNCTION get_account_transaction_overdraft_counts()
RETURNS TABLE (
    account_name TEXT,
    transactions_exceeding_balance INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_balances AS (
        SELECT
            ta.account_name,
            t.transaction_id,
            tb.spent_amount,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
                AS calculated_balance
        FROM transaction_account ta
        JOIN transaction_breakdown tb
            ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t
            ON tb.transaction_id = t.transaction_id
    )
    SELECT
        account_name,
        COUNT(transaction_id) AS transactions_exceeding_balance
    FROM cumulative_balances
    WHERE
        spent_amount > calculated_balance
        AND spent_amount > 0
    GROUP BY account_name
    ORDER BY transactions_exceeding_balance DESC;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Пресметка на кумулативен баланс по сметка:
- CB ← γ,,account_name, transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY transaction_account_id ORDER BY date) → calculated_balance,,(J2)

Филтрирање на трансакции што го надминуваат балансот:
- F ← σ,,spent_amount > calculated_balance ∧ spent_amount > 0,,(CB)

Броење на трансакции по сметка:
- R ← γ,,account_name; COUNT(transaction_id) → transactions_exceeding_balance,,(F)

Подредување по број на прекршувања:
- R_final ← τ,,transactions_exceeding_balance DESC,,(R)

== 

> Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_transaction_overdraft_count()
RETURNS TABLE (
    total_transactions_exceeding_balance INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_total_balances AS (
        SELECT
            t.transaction_id,
            tb.spent_amount,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY u.user_id ORDER BY t.date)
                AS calculated_total_balance
        FROM transaction_account ta
        JOIN "user" u
            ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb
            ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t
            ON tb.transaction_id = t.transaction_id
    )
    SELECT
        COUNT(transaction_id) AS total_transactions_exceeding_balance
    FROM cumulative_total_balances
    WHERE
        spent_amount > calculated_total_balance
        AND spent_amount > 0;
END;
$$;
}}}

==== Релациона алгебра
- U(user_id)
- TA(transaction_account_id, user_id)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T

Пресметка на кумулативен вкупен баланс по корисник:
- CT ← γ,,transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY user_id ORDER BY date) → calculated_total_balance,,(J3)

Филтрирање на трансакции што го надминуваат вкупниот баланс:
- F ← σ,,spent_amount > calculated_total_balance ∧ spent_amount > 0,,(CT)

Броење на сите прекршувачки трансакции:
- R_final ← γ,,; COUNT(transaction_id) → total_transactions_exceeding_balance,,(F)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport7
==== Сумарни податоци за тагови
Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
===== Вкупен број на трансакции
{{{#!sql
CREATE OR REPLACE FUNCTION get_monthly_transaction_count_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;
}}}

===== Релациона алгебра
- T(transaction_id, date)
- TAT(transaction_id, tag_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← T ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J2 ← J1 ⨝,,TAT.tag_id = TG.tag_id,, TG

Групирање по месец и таг:
- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; COUNT(transaction_id) → transaction_count,,(J2)

== 

===== Вкупно примени средства
{{{#!sql
CREATE OR REPLACE FUNCTION get_monthly_income_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;
}}}

==== Релациона алгебра
- T(transaction_id, date)
- TB(transaction_id, earned_amount)
- TAT(transaction_id, tag_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB
- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG

Групирање по месец и таг:
- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(earned_amount) → total_income,,(J3)

== 

===== Вкупно потрошени средства
{{{#!sql
CREATE OR REPLACE FUNCTION get_monthly_expense_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;
}}}

==== Релациона алгебра
- T(transaction_id, date)
- TB(transaction_id, spent_amount)
- TAT(transaction_id, tag_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB
- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG

Групирање по месец и таг:
- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(spent_amount) → total_expense,,(J3)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport8
==== Просечно трошење во последните 3 дена за цел систем
Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
{{{#!sql
CREATE OR REPLACE FUNCTION get_average_daily_spending_last_3_days_fixed()
RETURNS TABLE (
    average_spending_last_3_days NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        SUM(daily_spending) / 3 AS average_spending_last_3_days
    FROM (
        SELECT
            d.day::date AS transaction_date,
            COALESCE(SUM(tb.spent_amount), 0) AS daily_spending
        FROM (
            SELECT CURRENT_DATE - 2 AS day
            UNION ALL
            SELECT CURRENT_DATE - 1
            UNION ALL
            SELECT CURRENT_DATE
        ) d
        LEFT JOIN transaction t
            ON t.date::date = d.day
        LEFT JOIN transaction_breakdown tb
            ON t.transaction_id = tb.transaction_id
        GROUP BY d.day
    ) daily_totals;
END;
$$;
}}}

==== Релациона алгебра
- T(transaction_id, date)
- TB(transaction_id, spent_amount)
- D(day)   -- генерирана релација со последните 3 дена

Генерирање на последните 3 дена:
- D ← { CURRENT_DATE − 2, CURRENT_DATE − 1, CURRENT_DATE }

LEFT JOIN со трансакции:
- J1 ← D ⟕,,DATE(T.date) = D.day,, T
- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB

Групирање по ден и пресметка на дневно трошење:
- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J2)

Пресметка на просек (фиксно делење со 3):
- R_final ← γ,,; (Σ(daily_spending) / 3) → average_spending_last_3_days,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport9
==== Просечно трошење во последните 3 дена за корисник
Просечно дневно трошење за последните 3 дена за одреден корисник:
{{{#!sql
CREATE OR REPLACE FUNCTION get_average_daily_spending_last_3_days_user(
    p_user_id INT
)
RETURNS TABLE (
    average_spending_last_3_days NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        SUM(daily_spending) / 3 AS average_spending_last_3_days
    FROM (
        SELECT
            d.day::date AS transaction_date,
            COALESCE(SUM(tb.spent_amount), 0) AS daily_spending
        FROM (
            SELECT CURRENT_DATE - 2 AS day
            UNION ALL
            SELECT CURRENT_DATE - 1
            UNION ALL
            SELECT CURRENT_DATE
        ) d
        LEFT JOIN transaction_account ta
            ON ta.user_id = p_user_id
        LEFT JOIN transaction_breakdown tb
            ON ta.transaction_account_id = tb.transaction_account_id
        LEFT JOIN transaction t
            ON t.transaction_id = tb.transaction_id
            AND t.date::date = d.day
        GROUP BY d.day
    ) daily_totals;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- T(transaction_id, date)
- TB(transaction_id, spent_amount)
- D(day) -- генерирани последните 3 дена

Генерирање на последните 3 дена:
- D ← { CURRENT_DATE − 2, CURRENT_DATE − 1, CURRENT_DATE }

LEFT JOIN со сметка на корисник:
- J1 ← D ⟕,,TA.user_id = p_user_id,, TA
- J2 ← J1 ⟕,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J3 ← J2 ⟕,,TB.transaction_id = T.transaction_id ∧ DATE(T.date) = D.day,, T

Групирање по ден и пресметка на дневно трошење:
- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3)

Просечно дневно трошење (фиксно делење со 3):
- R_final ← γ,,; (Σ(daily_spending) / 3) → average_spending_last_3_days,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport25
==== Просечно трошење во последните N дена
- по корисник (со параметар p_user_id)
- за цел систем (со параметар p_user_id = NULL)

{{{#!sql
CREATE OR REPLACE FUNCTION get_average_daily_spending_last_N_days(
    p_days INT,
    p_user_id INT DEFAULT NULL  -- ако е NULL, се зема цел систем
)
RETURNS TABLE (
    average_spending NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        SUM(daily_spending) / p_days AS average_spending
    FROM (
        -- генерација на последни p_days
        SELECT
            d.day::date AS transaction_date,
            COALESCE(SUM(tb.spent_amount), 0) AS daily_spending
        FROM (
            SELECT generate_series(CURRENT_DATE - (p_days - 1), CURRENT_DATE, '1 day') AS day
        ) d
        LEFT JOIN transaction t
            ON t.date::date = d.day
        LEFT JOIN transaction_breakdown tb
            ON t.transaction_id = tb.transaction_id
        LEFT JOIN transaction_account ta
            ON tb.transaction_account_id = ta.transaction_account_id
            AND (p_user_id IS NULL OR ta.user_id = p_user_id)
        GROUP BY d.day
    ) daily_totals;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- T(transaction_id, date)
- TB(transaction_id, spent_amount)
- D(day) -- генерација на последните p_days

Генерација на последни p_days:
- D ← { CURRENT_DATE − (p_days − 1), ..., CURRENT_DATE }

LEFT JOIN со трансакции:
- J1 ← D ⟕,,TRUE,, T
- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB
- J3 ← J2 ⟕,,TB.transaction_account_id = TA.transaction_account_id ∧ (p_user_id IS NULL OR TA.user_id = p_user_id),, TA

Групирање по ден и пресметка на дневно трошење:
- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3)

Просечно дневно трошење (фиксно делење со p_days):
- R_final ← γ,,; (Σ(daily_spending) / p_days) → average_spending,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport10
==== Вкупно потрошено во тековниот месец за цел систем
Вкупна сума на трошоци во тековниот месец
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_spent_current_month()
RETURNS TABLE (
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t
        ON tb.transaction_id = t.transaction_id
    WHERE EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
END;
$$;
}}}

==== Релациона алгебра
- TB(transaction_id, spent_amount)
- T(transaction_id, date)

JOIN на табелите:
- J1 ← TB ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање на трансакции од тековниот месец и година:
- F ← σ,,EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J1)

Агрегација на вкупно потрошено:
- R_final ← γ,,; SUM(spent_amount) → total_spent,,(F)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport11
==== Вкупно потрошено во тековниот месец за корисник
Вкупна сума на трошоци во тековниот месец на еден корисник
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_spent_current_month_user(
    p_user_id INT
)
RETURNS TABLE (
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta
        ON tb.transaction_account_id = ta.transaction_account_id
    WHERE ta.user_id = p_user_id
      AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање на трансакции за даден корисник и тековен месец:
- F ← σ,,user_id = p_user_id ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),, (J2)

Агрегација на вкупно потрошено:
- R_final ← γ,,; SUM(spent_amount) → total_spent,,(F)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport12
==== Дневен буџет до крајот на месецот за корисник
Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
{{{#!sql
CREATE OR REPLACE FUNCTION get_daily_budget_user(
    p_user_id INT
)
RETURNS TABLE (
    daily_budget NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(ta.balance), 0) / 
        (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') 
         - DATE_PART('day', CURRENT_DATE) + 1) AS daily_budget
    FROM transaction_account ta
    WHERE ta.user_id = p_user_id;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id, balance)

Филтрирање по корисник:
- F ← σ,,user_id = p_user_id,,(TA)

Агрегација на вкупен баланс:
- S ← γ,,; SUM(balance) → total_balance,,(F)

Пресметка на дневен буџет до крајот на месецот:
- R_final ← γ,,; total_balance / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY')- DATE_PART('day', CURRENT_DATE) + 1) → daily_budget,,(S)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport13
==== Долг на кредитна картичка од минатиот месец за цел систем
Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
{{{#!sql
CREATE OR REPLACE FUNCTION get_credit_card_debt_last_month()
RETURNS TABLE (
    credit_card_debt NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(tb.spent_amount), 0) AS credit_card_debt
    FROM transaction_breakdown tb
    JOIN transaction_account ta
        ON tb.transaction_account_id = ta.transaction_account_id
    JOIN transaction t
        ON tb.transaction_id = t.transaction_id
    WHERE (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
      AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
END;
}}}

==== Релациона алгебра
- TA(transaction_account_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање на кредитни картички и минат месец:
- F ← σ,,(account_name ILIKE '%кредитна%' OR account_name ILIKE '%credit%') ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'),,(J2)

Агрегација на вкупен долг:
- R_final ← γ,,; SUM(spent_amount) → credit_card_debt,,(F)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport14
==== Долг на кредитна картичка од минатиот месец за корисник
Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
{{{#!sql
CREATE OR REPLACE FUNCTION get_credit_card_debt_last_month_user(
    p_user_id INT
)
RETURNS TABLE (
    credit_card_debt NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(tb.spent_amount), 0) AS credit_card_debt
    FROM transaction_breakdown tb
    JOIN transaction_account ta
        ON tb.transaction_account_id = ta.transaction_account_id
    JOIN transaction t
        ON tb.transaction_id = t.transaction_id
    WHERE ta.user_id = p_user_id
      AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
      AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање по корисник, кредитна картичка и минат месец:
- F ← σ,,user_id = p_user_id ∧ (account_name ILIKE '%кредитна%' OR account_name ILIKE '%credit%') ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'),,(J2)

Агрегација на вкупен долг:
- R_final ← γ,,; SUM(spent_amount) → credit_card_debt,,(F)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport15
==== Трендови на трошење според тагови за цел систем
Трендови на трошење за секој таг во последните шест месеци
{{{#!sql
CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months()
RETURNS TABLE (
    tag_name TEXT,
    month DATE,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        DATE_TRUNC('month', t.date) AS month,
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM tag tg
    JOIN tag_assigned_to_transaction tat 
        ON tg.tag_id = tat.tag_id
    JOIN transaction t 
        ON tat.transaction_id = t.transaction_id
    JOIN transaction_breakdown tb 
        ON t.transaction_id = tb.transaction_id
    WHERE t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    GROUP BY tg.tag_name, month
    ORDER BY tg.tag_name, month;
END;
$$;
}}}

==== Релациона алгебра
- TG(tag_id, tag_name)
- TAT(tag_id, transaction_id)
- T(transaction_id, date)
- TB(transaction_id, spent_amount)

JOIN на сите табели:
- J1 ← TG ⨝,,TG.tag_id = TAT.tag_id,, TAT
- J2 ← J1 ⨝,,TAT.transaction_id = T.transaction_id,, T
- J3 ← J2 ⨝,,T.transaction_id = TB.transaction_id,, TB

Филтрирање на трансакции од последните 6 месеци:
- F ← σ,,date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS'),,(J3)

Групирање по таг и месец:
- G ← γ,,tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent,,(F)

Подредување по таг и месец:
- R_final ← τ,,tag_name, month,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport16
==== Трендови на трошење според тагови за корисник
Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
{{{#!sql
CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months_user(
    p_user_id INT
)
RETURNS TABLE (
    tag_name TEXT,
    month DATE,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        DATE_TRUNC('month', t.date) AS month,
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM tag_assigned_to_transaction tat
    JOIN transaction t 
        ON tat.transaction_id = t.transaction_id
    JOIN transaction_breakdown tb 
        ON t.transaction_id = tb.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    JOIN tag tg 
        ON tat.tag_id = tg.tag_id
    WHERE ta.user_id = p_user_id
      AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    GROUP BY tg.tag_name, month
    ORDER BY tg.tag_name, month;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- T(transaction_id, date)
- TB(transaction_id, transaction_account_id, spent_amount)
- TAT(tag_id, transaction_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T
- J3 ← J2 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J4 ← J3 ⨝,,TAT.tag_id = TG.tag_id,, TG

Филтрирање по корисник и последни 6 месеци:
- F ← σ,,user_id = p_user_id ∧ date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS'),,(J4)

Групирање по таг и месец:
- G ← γ,,tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent,,(F)

Подредување по таг и месец:
- R_final ← τ,,tag_name, month,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport17
==== Вкупно трошење според тагови
Вкупно трошење групирано според тагови за тековниот месец
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_spent_by_tag_current_month()
RETURNS TABLE (
    tag_name TEXT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name,
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM tag tg
    JOIN tag_assigned_to_transaction tat 
        ON tg.tag_id = tat.tag_id
    JOIN transaction t 
        ON tat.transaction_id = t.transaction_id
    JOIN transaction_breakdown tb 
        ON t.transaction_id = tb.transaction_id
    WHERE EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY tg.tag_name
    ORDER BY total_spent DESC;
END;
$$;
}}}

==== Релациона алгебра
- TG(tag_id, tag_name)
- TAT(tag_id, transaction_id)
- T(transaction_id, date)
- TB(transaction_id, spent_amount)

JOIN на сите табели:
- J1 ← TG ⨝,,TG.tag_id = TAT.tag_id,, TAT
- J2 ← J1 ⨝,,TAT.transaction_id = T.transaction_id,, T
- J3 ← J2 ⨝,,T.transaction_id = TB.transaction_id,, TB

Филтрирање по тековен месец и година:
- F ← σ,,EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J3)

Групирање и агрегација по таг:
- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(F)

Подредување по вкупно потрошено:
- R_final ← τ,,total_spent DESC,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport18
==== Сметки со највисоко вкупно трошење во изминатата година
{{{#!sql
CREATE OR REPLACE FUNCTION get_top_accounts_last_year()
RETURNS TABLE (
    account_name TEXT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ta.account_name, 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    WHERE t.date >= NOW() - INTERVAL '1 YEAR'
    GROUP BY ta.account_name
    ORDER BY total_spent DESC
    LIMIT 10;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на табелите:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање по последна година:
- F ← σ,,date >= NOW() - INTERVAL '1 YEAR',, (J2)

Групирање и агрегација по сметка:
- G ← γ,,account_name; SUM(spent_amount) → total_spent,,(F)

Подредување по вкупно потрошено и лимитирање:
- R_final ← τ,,total_spent DESC LIMIT 10,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport19
==== Најчести времиња за трансакции
Во кој час од денот корисниците најчесто вршат трансакции
{{{#!sql
CREATE OR REPLACE FUNCTION get_most_frequent_transaction_hours()
RETURNS TABLE (
    transaction_hour INT,
    transaction_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        EXTRACT(HOUR FROM t.date)::INT AS transaction_hour, 
        COUNT(*) AS transaction_count
    FROM transaction t
    GROUP BY transaction_hour
    ORDER BY transaction_count DESC;
END;
$$;
}}}

==== Релациона алгебра
- T(transaction_id, date)

Групирање по час на трансакција:
- G ← γ,,transaction_hour = EXTRACT(HOUR FROM date); COUNT(transaction_id) → transaction_count,,(T)

Подредување по број на трансакции:
- R_final ← τ,,transaction_count DESC,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport20
==== Трансакции според тагови со највисоко трошење за цел систем
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_spent_by_tag_system()
RETURNS TABLE (
    tag_name TEXT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN tag_assigned_to_transaction tat 
        ON t.transaction_id = tat.transaction_id
    JOIN tag tg 
        ON tat.tag_id = tg.tag_id
    GROUP BY tg.tag_name
    ORDER BY total_spent DESC;
END;
$$;
}}}

==== Релациона алгебра
- TB(transaction_id, spent_amount)
- T(transaction_id, date)
- TAT(tag_id, transaction_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← TB ⨝,,TB.transaction_id = T.transaction_id,, T
- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG

Групирање и агрегација по таг:
- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(J3)

Подредување по вкупно потрошено:
- R_final ← τ,,total_spent DESC,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport21
==== Трансакции според тагови со највисоко трошење за корисник
Сумирање на трошењата според тагови за одреден корисник
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_spent_by_tag_user(
    p_user_id INT
)
RETURNS TABLE (
    tag_name TEXT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    JOIN tag_assigned_to_transaction tat 
        ON t.transaction_id = tat.transaction_id
    JOIN tag tg 
        ON tat.tag_id = tg.tag_id
    WHERE ta.user_id = p_user_id
    GROUP BY tg.tag_name
    ORDER BY total_spent DESC;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)
- TAT(tag_id, transaction_id)
- TG(tag_id, tag_name)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T
- J3 ← J2 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
- J4 ← J3 ⨝,,TAT.tag_id = TG.tag_id,, TG

Филтрирање по корисник:
- F ← σ,,user_id = p_user_id,,(J4)

Групирање и агрегација по таг:
- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(F)

Подредување по вкупно потрошено:
- R_final ← τ,,total_spent DESC,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport22
==== Годишни трендови на трансакции за цел систем
Трошења според сметки за секој квартал од тековната година
{{{#!sql
CREATE OR REPLACE FUNCTION get_quarterly_spending_trends_system()
RETURNS TABLE (
    account_name TEXT,
    q1_spent NUMERIC,
    q2_spent NUMERIC,
    q3_spent NUMERIC,
    q4_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ta.account_name, 
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    WHERE EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY ta.account_name
    ORDER BY ta.account_name;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на табелите:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање по тековна година:
- F ← σ,,EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J2)

Агрегација по квартали:
- G ← γ,,account_name; SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 1 THEN spent_amount ELSE 0 END) → q1_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 2 THEN spent_amount ELSE 0 END) → q2_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 3 THEN spent_amount ELSE 0 END) → q3_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 4 THEN spent_amount ELSE 0 END) → q4_spent,,(F)

Подредување по име на сметка:
- R_final ← τ,,account_name,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport23
==== Годишни трендови на трансакции за корисник
Трошења по квартали за тековната година за одреден корисник
{{{#!sql
CREATE OR REPLACE FUNCTION get_quarterly_spending_trends_user(
    p_user_id INT
)
RETURNS TABLE (
    account_name TEXT,
    q1_spent NUMERIC,
    q2_spent NUMERIC,
    q3_spent NUMERIC,
    q4_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ta.account_name, 
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    WHERE ta.user_id = p_user_id
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY ta.account_name
    ORDER BY ta.account_name;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount)
- T(transaction_id, date)

JOIN на табелите:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Филтрирање по корисник и тековна година:
- F ← σ,,user_id = p_user_id ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J2)

Агрегација по квартали:
- G ← γ,,account_name; SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 1 THEN spent_amount ELSE 0 END) → q1_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 2 THEN spent_amount ELSE 0 END) → q2_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 3 THEN spent_amount ELSE 0 END) → q3_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 4 THEN spent_amount ELSE 0 END) → q4_spent,,(F)

Подредување по име на сметка:
- R_final ← τ,,account_name,,(G)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport24
==== Неактивни тагови
Идентификување тагови кои не биле користени во изминатиот месец
{{{#!sql
CREATE OR REPLACE FUNCTION get_inactive_tags_last_month()
RETURNS TABLE (
    tag_name TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name
    FROM tag tg
    LEFT JOIN tag_assigned_to_transaction tat 
        ON tg.tag_id = tat.tag_id
    LEFT JOIN transaction t 
        ON tat.transaction_id = t.transaction_id
    WHERE t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
END;
$$;
}}}

==== Релациона алгебра
- TG(tag_id, tag_name)
- TAT(tag_id, transaction_id)
- T(transaction_id, date)

LEFT JOIN на табелите:
- J1 ← TG ⟕,,TG.tag_id = TAT.tag_id,, TAT
- J2 ← J1 ⟕,,TAT.transaction_id = T.transaction_id,, T

Филтрирање на неактивни тагови:
- R_final ← σ,,date IS NULL ∨ date < NOW() - INTERVAL '1 MONTH',,(J2)
https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReportsVersion1
= Напредни извештаи од базата (SQL и складирани процедури)

===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
{{{#!sql
WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        t.transaction_name,
        t.date AS transaction_date,
        ta.account_name,
        u.user_id,
        u.user_name,
        tb.spent_amount AS transaction_amount,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
    WHERE 
        ta.account_name = 'Specific Account Name' -- Може да се замени со специфично име на акаунт
)
SELECT 
    user_id,
    user_name,
    account_name,
    transaction_id,
    transaction_name,
    transaction_amount,
    transaction_date,
    calculated_balance
FROM 
    CumulativeBalances
WHERE 
    transaction_amount > calculated_balance -- Трансакцијата го надминува пресметаниот баланс
    AND transaction_amount > 0
ORDER BY 
    user_id, account_name, transaction_date DESC;
}}}

===== Надминување на баланс на една сметка со трансакција во сегашно време
{{{#!sql
SELECT 
    u.user_id,
    u.user_name,
    ta.account_name,
    ta.balance AS current_balance,
    t.transaction_id,
    t.transaction_name,
    tb.spent_amount AS transaction_amount,
    t.date AS transaction_date
FROM 
    transaction_account ta
JOIN user u ON ta.user_id = u.user_id
JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    tb.spent_amount > ta.balance -- Трансакцијата го надминува моменталниот баланс на СМЕТКАТА
    AND tb.spent_amount > 0
ORDER BY 
    u.user_id, ta.account_name, t.date DESC;
}}}

===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
{{{#!sql
WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        t.transaction_name,
        t.date AS transaction_date,
        u.user_id,
        u.user_name,
        tb.spent_amount AS transaction_amount,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    user_id,
    user_name,
    transaction_id,
    transaction_name,
    transaction_amount,
    transaction_date,
    calculated_total_balance
FROM 
    CumulativeTotalBalances
WHERE 
    transaction_amount > calculated_total_balance -- Трансакцијата го надминува пресметаниот вкупен баланс од сите сметки
    AND transaction_amount > 0
ORDER BY 
    user_id, transaction_date DESC;
}}}

===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност
{{{#!sql
WITH total_user_balance AS (
    SELECT 
        u.user_id,
        SUM(ta.balance) AS total_balance
    FROM 
        transaction_account ta
    JOIN user u ON ta.user_id = u.user_id
    GROUP BY 
        u.user_id
)
SELECT 
    u.user_id,
    u.user_name,
    SUM(tb.spent_amount) AS total_transaction_amount,
    tub.total_balance AS user_total_balance
FROM 
    transaction_account ta
JOIN user u ON ta.user_id = u.user_id
JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN total_user_balance tub ON u.user_id = tub.user_id
WHERE 
    t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
GROUP BY 
    u.user_id, u.user_name, tub.total_balance
HAVING 
    SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
ORDER BY 
    u.user_id;
}}}

===== Сумарни приходи и расходи по месеци
Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
{{{#!sql
SELECT 
    TO_CHAR(t.date, 'YYYY-MM') AS month, -- Форматирање на датумот во формат "Година-Месец"
    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) AS total_income, -- Вкупен приход
    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS total_expense, -- Вкупен расход
    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) -
    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS net_balance -- Нето состојба
FROM 
    transaction_breakdown tb
JOIN 
    transaction t ON tb.transaction_id = t.transaction_id
GROUP BY 
    TO_CHAR(t.date, 'YYYY-MM') -- Групирање по месец
ORDER BY 
    month;
}}}
Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците

===== Број на трансакции што ги надминуваат приходите на сметка
{{{#!sql
WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        ta.account_name,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    account_name, 
    COUNT(transaction_id) AS transactions_exceeding_balance
FROM 
    CumulativeBalances
WHERE 
    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    AND spent_amount > 0
GROUP BY 
    account_name
ORDER BY 
    transactions_exceeding_balance DESC;
}}}
Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
{{{#!sql
WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        u.user_id,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    COUNT(transaction_id) AS total_transactions_exceeding_balance
FROM 
    CumulativeTotalBalances
WHERE 
    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    AND spent_amount > 0;
}}}

===== Сумарни податоци за тагови
Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
 - Вкупен број на трансакции
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}
 - Вкупно примени средства
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}
 - Вкупно потрошени средства
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}

===== Просечно трошење во последните 3 дена за цел систем
Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
{{{#!sql
SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN 
        transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    WHERE 
        t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;
}}}

===== Просечно трошење во последните 3 дена за корисник
Просечно дневно трошење за последните 3 дена за одреден корисник:
{{{#!sql
SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    WHERE 
        ta.user_id = 101 -- ID на конкретниот корисник
        AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;
}}}

===== Вкупно потрошено во тековниот месец за цел систем
Вкупна сума на трошоци во тековниот месец
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
}}}

===== Вкупно потрошено во тековниот месец за корисник
Вкупна сума на трошоци во тековниот месец на еден корисник
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
}}}

===== Дневен буџет до крајот на месецот за корисник
Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
{{{#!sql
SELECT 
    SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget
FROM 
    transaction_account ta
WHERE 
    ta.user_id = 101; -- ID на конкретниот корисник
}}}

===== Долг на кредитна картичка од минатиот месец за цел систем
Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
}}}

===== Долг на кредитна картичка од минатиот месец за корисник
Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
}}}

===== Трендови на трошење според тагови за цел систем
Трендови на трошење за секој таг во последните шест месеци
{{{#!sql
SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;
}}}

===== Трендови на трошење според тагови за корисник
Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
{{{#!sql
SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag_assigned_to_transaction tat
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;
}}}

===== Вкупно трошење според тагови
Вкупно трошење групирано според тагови за тековниот месец
{{{#!sql
SELECT 
    tg.tag_name,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Сметки со највисоко вкупно трошење во изминатата година
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY 
    ta.account_name
ORDER BY 
    total_spent DESC
LIMIT 10;
}}}

===== Најчести времиња за трансакции
Во кој час од денот корисниците најчесто вршат трансакции
{{{#!sql
SELECT 
    EXTRACT(HOUR FROM t.date) AS transaction_hour, 
    COUNT(*) AS transaction_count
FROM 
    transaction t
GROUP BY 
    transaction_hour
ORDER BY 
    transaction_count DESC;
}}}

===== Трансакции според тагови со највисоко трошење за цел систем
{{{#!sql
SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
JOIN tag tg ON tat.tag_id = tg.tag_id
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Трансакции според тагови со највисоко трошење за корисник
Сумирање на трошењата според тагови за одреден корисник
{{{#!sql
SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
JOIN tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Годишни трендови на трансакции за цел систем
Трошења според сметки за секој квартал од тековната година
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;
}}}

===== Годишни трендови на трансакции за корисник
Трошења по квартали за тековната година за одреден корисник
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;
}}}

===== Неактивни тагови
Идентификување тагови кои не биле користени во изминатиот месец
{{{#!sql
SELECT 
    tg.tag_name
FROM 
    tag tg
LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
WHERE 
    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
}}}

https://develop.finki.ukim.mk/projects/FEIN/wiki/PrototypeApplication
= Прототип апликација

== Презентација на финалниот изглед на апликацијата
[wiki:Presentation Презентација]

== Инструкции за стартување на апликацијата
[wiki:Instructions Инструкции]


https://develop.finki.ukim.mk/projects/FEIN/wiki/Presentation
== Опис на финална имплементација со функционалности и насоки за употреба

Финалната верзија на апликацијата ги опфаќа сите сценарија од фаза Ф3, а тоа се:
- Регистрација на корисник
- Најава на корисник
- Додавање на трансакциска сметка
- Преглед на сметки
- Додавање на трансакција
- Преглед на трансакции
- Ажурирање/бришење трансакции
- Извештаи (годишни/месечни/по тагови/графици)

== Опис на секое од наведените сценарија
=== Регистрација на корисник
[https://youtu.be/9s8dOSDpf38 видео]
=== Најава на корисник
[https://youtu.be/elvXAcs-mVA видео]
=== Додавање на трансакциска сметка
[https://youtu.be/jfItnKE0whg видео]
=== Преглед на сметки
[https://youtu.be/DxqRu2akhJ0 видео]
=== Додавање на трансакција
[https://youtu.be/BxtTVXZDi2A видео]
=== Преглед на трансакции
[https://youtu.be/jZQE1guSBPo видео]
=== Ажурирање трансакции
[https://youtu.be/vxQXTLj2HgE видео]
=== Бришење трансакции
[https://youtu.be/pET4XEN1pxM видео]
=== Извештаи (годишни/месечни/по тагови/графици)
[https://youtu.be/bkKTstZj0uU видео]

https://develop.finki.ukim.mk/projects/FEIN/wiki/Instructions
== Упатство за стартување и користење на апликацијата (Верзија 2)

Препорачувам користење на Visual Studio Code.
Потребно е да се инсталира Python, истото може да се направи [https://www.python.org/downloads/ овде].

Во VS Code, препорачувам користење на виртуелна околина, пример //venv//. Повеќе на [https://docs.python.org/3/library/venv.html линков].

Откако ќе се активира виртуелна околина, може да се инсталираат потребните python пакети во нејзе. Тие се наоѓаат во текстуелната датотека //requirements.txt//. За да се инсталираат сите одеднаш, може да се искорсти командата: 
{{{#!cmd
pip install -r requirements.txt
}}}

Кога сѐ од горенаведеното ќе се направи, може да се отворат две //bash// терминали во VS Code кои ќе се постават една до друга (root е root-от на проектот):
- Во првата терминала се пишува командата
{{{#!cmd
uvicorn app.main:app --host 0.0.0.0 --port 8000 --reload
}}}
    со неа се стартува backend-от кој се поврзува автоматски со датабазата на проектот дадена од професорот.
- Во втората терминала се пишува командата
{{{#!cmd
python -m cli.cli_app
}}}
    со неа се извршува прототип апликацијата која се наоѓа во //cli// директориумот под името //cli_app//.

Во втората терминала може да се почне со користење на прототип апликацијата, притоа секое повикување и користење на backend-от и базата се отсликува во првата терминала. 

=== Верзии
[wiki:InstructionsVersion1 Верзија 1]

https://develop.finki.ukim.mk/projects/FEIN/wiki/InstructionsVersion1
== Упатство за стартување и користење на апликацијата (Верзија 1)

Препорачувам користење на Visual Studio Code.
Потребно е да се инсталира Python, истото може да се направи [https://www.python.org/downloads/ овде].

Во VS Code, препорачувам користење на виртуелна околина, пример //venv//. Повеќе на [https://docs.python.org/3/library/venv.html линков].

Откако ќе се активира виртуелна околина, може да се инсталираат потребните python пакети во нејзе. Тие се наоѓаат во текстуелната датотека //requirements.txt//. За да се инсталираат сите одеднаш, може да се искорсти командата: 
{{{#!cmd
pip install -r requirements.txt
}}}

Јас користев Docker Containers за извршување и поврзување на прототип апликацијата со локална датабаза заедно. За тоа потребно е да се инсталира Docker Desktop, инструкции за тоа на [https://docs.docker.com/desktop/setup/install/windows-install/ линков].

Кога сѐ од горенаведеното ќе се направи, може да се отворат две //bash// терминали во VS Code кои ќе се постават една до друга (root е root-от на проектот):
- Во првата терминала се пишува командата
{{{#!cmd
docker-compose up --build
}}}
    со неа се build-аат и подигаат Docker контејнерите. Првиот пат, процесот ќе потрае подолго, бидејќи треба да ги симне и инсталира Docker сликите, истото да го направи со python пакетите и сето тоа да го из-build-а.
- Во втората терминала се пишува командата
{{{#!cmd
python -m cli.cli_app
}}}
    со неа се извршува прототип апликацијата која се наоѓа во //cli// директориумот под името //cli_app//.

Во втората терминала може да се почне со користење на прототип апликацијата, притоа секое повикување и користење на backend-от и базата се отсликува во првата терминала.


https://develop.finki.ukim.mk/projects/FEIN/wiki/Normalization
= Нормализација

== Денормализирана форма

Форма во кој има една табела во која се внесени сите ентитети и нивни релации помеѓу себе, без никакви правила

||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =||
|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 1, 2 || credit card, cash || 5000, 1200 || 1, 2 || electricity bill, burger || -1800, -800 || -1800, -200 || 18:03:25 Sep 26, 2025, 12:43:00 Sep 26, 2025 || 1,2 || -1800, -800 || 0, 600 || 1, 2 || bills, food ||
|| 2|| anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || 1 || debit card || 3200 || 1 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || 1 || -200 || 0 || 1 || beauty ||

Во денормализираната табела постојат следниве основни функционални зависности:

- {user_id} → {user_name, email, password}
- {transaction_account_id} → {account_name, balance, user_id}
- {transaction_id} → {transaction_name, amount, net_amount, date}
- {transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount}
- {tag_id} → {tag_name}

== Прва нормална форма

Форма во која повторно има една табела, но овојпат, таа е ограничена со следниве работи:
- Подредувањето на редовите не претставува никакво значење
- Во ќелиите на секоја колона има по една вредност
- Не се мешаат типови на податоци во една ќелија
- Табелата има примарен композитен клуч(user_id, transaction_id, transaction_account_id, transaction_breakdown_id)
- Нема повторувачки групи, сите што биле во денормализираната форма сега се во посебен ред 

==== R
||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =||
|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 1|| credit card || 5000|| 1|| electricity bill || -1800|| -1800|| 18:03:25 Sep 26, 2025 || 1|| -1800|| 0|| 1|| bills ||
|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 2|| cash || 1200|| 2|| burger || -800|| -200|| 12:43:00 Sep 26, 2025 || 2|| -800|| 600|| 2|| food ||
|| 2|| anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || 3|| debit card || 3200|| 3|| nail polish || -200|| -200|| 19:31:32 Sep 12 2025 || 3|| -200|| 0|| 3|| beauty ||

==== Избор на примарен клуч во 1NF

Во првата нормална форма е дефиниран композитен примарен клуч:
//(user_id, transaction_id, transaction_account_id, transaction_breakdown_id)//

tag_id може да биде вклучен во композитниот примарен клуч во 1NF бидејќи 1NF само ограничува по атомност и уникатност на ред, но не треба да биде вклучен бидејќи ја нарушува минималноста на клучот, додава парцијални зависности и погрешно ја моделира many-to-many релацијата.
Затоа, tag_id мора да биде одделен во посебна релација во погорните нормални форми.

Овој клуч е избран бидејќи eдинствено го идентификува секој ред, ги опфаќа сите повторувачки групи од денормализираната форма и не постои подмножество од овој клуч кое може самостојно да го идентификува редот.

=== Премин од 1NF во 2NF
Проблем се делумните зависности, во 1NF постојат атрибути кои зависат само од дел од примарниот клуч, на пример:
- {user_id} → {user_name, email, password}
- {transaction_id} → {transaction_name, amount, net_amount, date}

Ова претставува кршење на втората нормална форма, решение за тоа е декомпозиција, односно секоја група на атрибути која зависи од дел од композитниот клуч се издвојува во посебна табела каде тој дел станува примарен клуч. Пример:
- USER(user_id PK, user_name, email, password)

== Втора нормална форма

Форма која ги следи овие правила:
- Веќе е во прва нормална форма
- Секој од атрибутите кој не е клуч, зависи од целосниот примарен клуч - со тоа се спречуваат аномалии на внесување, бришење и менување

==== R1

{ user_id } → {user_name, email, password}

||= USER =||=  =||=  =||=  =||
||= //user_id =||= //user_name =||= //email =||= //password =||
|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu ||
|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm ||

====== Lossless join test

R:
||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =||
R1:
||= //user_id =||= //user_name =||= //email =||= //password =||

R ∩ R1 = { user_id, user_name, email, password }

Бидејќи {user_id} → {user_name, email, password}, а user_id ∈ (R ∩ R1), следи дека (R ∩ R1) → R1

⇒ Декомпозицијата е lossless

R1.1 = R - { user_name, email, password }

==== R2 

{transaction_account_id} → {account_name, balance, user_id}

||= TRANSACTION_ACCOUNT =||=  =||=  =||= ||
||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id ||
|| 1 || credit card || 5000 || 1 ||
|| 2 || cash || 1200 || 1 ||
|| 3 || debit card || 3200 || 2 ||

====== Lossless join test

R1.1:
||= //user_id =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =||
R2:
||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id ||

R1.1 ∩ R2 = { transaction_account_id, account_name, balance, user_id }

Бидејќи {transaction_account_id} → {account_name, balance, user_id}, а transaction_account_id ∈ (R1.1 ∩ R2), следи дека (R1.1 ∩ R2) → R2

⇒ Декомпозицијата е lossless

R2.1 = R1.1 - { account_name, balance, user_id }

==== R3

{transaction_id} → {transaction_name, amount, net_amount, date, tag_id}

{transaction_id} → {tag_id} → {tag_name}

||= TRANSACTION_WITH_TAG_NAME =||=  =||=  =||=  =||=  =||=  =||=  =||
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =||
|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 || 1 || bills ||
|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 || 2 || food ||
|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || 3 || beauty ||

====== Lossless join test

R2.1:
||= //transaction_account_id =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =||
R3:
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =||

R2.1 ∩ R3 = { transaction_id, transaction_name, amount, net_amount, date, tag_id, tag_name }

Бидејќи {transaction_id} → {transaction_name, amount, net_amount, date, tag_id, tag_name}, а transaction_id ∈ (R2.1 ∩ R3), следи дека (R2.1 ∩ R3) → R3

⇒ Декомпозицијата е lossless

R3.1 = R2.1 - { transaction_name, amount, net_amount, date, tag_id, tag_name }

==== R4

{transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount}

||= TRANSACTION_BREAKDOWN =||=  =||=  =||=  =||=  =||
||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =||
|| 1 || 1 || 1 || -1800 || 0 ||
|| 2 || 2 || 2 || -800 || 600 ||
|| 3 || 3 || 3 || -200 || 0 ||

====== Lossless join test

R3.1:
||= //transaction_account_id =||= //transaction_id =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||
R4:
||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =||

R3.1 ∩ R4 = { transaction_breakdown_id, transaction_id, transaction_account_id, spent_amount, earned_amount }

Бидејќи {transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount}, а transaction_breakdown_id ∈ (R3.1 ∩ R4), следи дека (R3.1 ∩ R4) → R4

⇒ Декомпозицијата е lossless

R4.1 = R3.1 - { transaction_breakdown_id, transaction_id, transaction_account_id, spent_amount, earned_amount }

R4.1 = ∅

=== Премин од 2NF во 3NF

Во 2NF, за поедноставување, се разгледува случај со еден таг по трансакција, оваа претпоставка се отстранува при премин во 3NF.

Проблем се транзитивните зависности, кои во 2NF сè уште постојат, на пример:
- {transaction_id} → {tag_id} → {tag_name}

Ова значи дека некои атрибути кои не се клучеви зависат од други атрибути кои исто така не се клучеви, што ја крши 3NF.

Решение за ова е отстранување на транзитивноста, односно секој атрибут кој не зависи директно од примарниот клуч се издвојува во нова табела. Пример:
- TAG(tag_id PK, tag_name)
- TAG_ASSIGNED_TO_TRANSACTION(transaction_id PK, tag_id PK)

== Трета нормална форма

Формава ги следи овие правила:
- Веќе е во втора нормална форма
- Се отргнуваат транзитивните функционални зависности, со тоа се овозможува сите атрибути кои не се клучеви да зависат само од примарниот клуч и од никој друг атрибут

==== R1

{user_id} → {user_name, email, password}

||= USER =||=  =||=  =||=  =||
||= //user_id =||= //user_name =||= //email =||= //password =||
|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu ||
|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm ||

==== R2

{transaction_account_id} → {account_name, balance, user_id}

||= TRANSACTION_ACCOUNT =||=  =||=  =||=  =||
||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id =||
|| 1 || credit card || 5000 || 1 ||
|| 2 || cash || 1200 || 1 ||
|| 3 || debit card || 3200 || 2 ||

==== R3.2

{transaction_id} → {transaction_name, amount, net_amount, date}

||= TRANSACTION =||=  =||=  =||=  =||=  =||
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||
|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 ||
|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 ||
|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 ||

====== Lossless join test

R3:
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =||
R3.2:
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||

R3 ∩ R3.2 = { transaction_id, transaction_name, amount, net_amount, date }

Бидејќи {transaction_id} → {transaction_name, amount, net_amount, date}, а transaction_id ∈ (R3 ∩ R3.2), следи дека (R3 ∩ R3.2) → R3.2

⇒ Декомпозицијата е lossless

R3.3 = R3 - { transaction_id, transaction_name, amount, net_amount, date }

==== R3.4

{tag_id} → {tag_name}

||= TAG =||=  =||
||= //tag_id =||= //tag_name =||
|| 1 || bills ||
|| 2 || food ||
|| 3 || beauty ||

====== Lossless join test

R3.3:
||= //tag_id =||= //tag_name =||
R3.4:
||= //tag_id =||= //tag_name =||

R3.3 ∩ R3.4 = { tag_id, tag_name }

Бидејќи {tag_id} → {tag_name}, а tag_id ∈ (R3.3 ∩ R3.4), следи дека (R3.3 ∩ R3.4) → R3.4

⇒ Декомпозицијата е lossless

R3.5 = R3.3 - { tag_id, tag_name }

R3.5 = ∅

==== R4

{transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount}

||= TRANSACTION_BREAKDOWN =||=  =||=  =||=  =||=  =||
||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =||
|| 1 || 1 || 1 || -1800 || 0 ||
|| 2 || 2 || 2 || -800 || 600 ||
|| 3 || 3 || 3 || -200 || 0 ||

==== R5

{transaction_id, tag_id} → {}

||= TAG_ASSIGNED_TO_TRANSACTION =||=  =||
||= //transaction_id =||= //tag_id =||
|| 1 || 1 ||
|| 2 || 2 ||
|| 3 || 3 ||

==== Избор на примарни клучеви во 3NF
По правилото дека секој примарен клуч мора минимално и единствено да го идентификува редот.
Примери:
- USER.user_id — природен идентификатор
- TRANSACTION.transaction_id — сурогатен клуч
- TAG_ASSIGNED_TO_TRANSACTION(transaction_id, tag_id) — композитен клуч за M:N релација
- TRANSACTION_BREAKDOWN.transaction_breakdown_id — сурогатен клуч

==== Третата нормална форма погоре исто така е и BCNF

=== 

Сите функционални зависности можат да се проверат локално во поединечни табели, без потреба од JOIN операции, oва значи дека шемата е dependency-preserving, lossless-join и е во BCNF.

== Заклучок
Со примената на формална анализа на функционални зависности, правилен избор на примарни клучеви и проверка со lossless-join тест се елиминираат сите аномалии, се обезбедува логичка коректност и се добива шема погодна за имплементација во релациона датабаза.

Финални табели:
||= USER =||=  =||=  =||=  =||
||= //user_id =||= //user_name =||= //email =||= //password =||
|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu ||
|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm ||
//
||= TRANSACTION_ACCOUNT =||=  =||=  =||=  =||
||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id =||
|| 1 || credit card || 5000 || 1 ||
|| 2 || cash || 1200 || 1 ||
|| 3 || debit card || 3200 || 2 ||
//
||= TRANSACTION =||=  =||=  =||=  =||=  =||
||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||
|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 ||
|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 ||
|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 ||
//
||= TAG =||=  =||
||= //tag_id =||= //tag_name =||
|| 1 || bills ||
|| 2 || food ||
|| 3 || beauty ||
//
||= TRANSACTION_BREAKDOWN =||=  =||=  =||=  =||=  =||
||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =||
|| 1 || 1 || 1 || -1800 || 0 ||
|| 2 || 2 || 2 || -800 || 600 ||
|| 3 || 3 || 3 || -200 || 0 ||
//
||= TAG_ASSIGNED_TO_TRANSACTION =||=  =||
||= //transaction_id =||= //tag_id =||
|| 1 || 1 ||
|| 2 || 2 ||
|| 3 || 3 ||

== 

[wiki:NormalizationVer1 Верзија 1]
https://develop.finki.ukim.mk/projects/FEIN/wiki/NormalizationVer1
= Нормализација

Ја дизајнирав базата на податоци со surrogate примарни клучеви(system-generated identifier) со една атрибутна колона (на пр. {{{user_id}}}, {{{transaction_id }}}) за едноставност и конзистентност. Вака, секој атрибут кој што не е клуч, директно зависи од примарниот клуч, што значи дека шемата ги исполнува 1NF, 2NF и 3NF/BCNF.

Единствената намерна редундантност е колоната {{{net_amount}}} во {{{TRANSACTION}}}. Ја вклучив затоа што овозможува побрзи пребарувања и извештаи, иако е изведлива од {{{TRANSACTION_BREAKDOWN}}}. Ова беше свесен компромис заради перформанси.

Кај many-to-many табелите ({{{TRANSACTION_BREAKDOWN}}} и {{{TAG_ASSIGNED_TO_TRANSACTION}}}) користам surrogate клучеви заради конзистентност на целата шема иако композитни клучеви би биле построго нормализирано решение, surrogate пристапот обезбедува унифицирана структура и јасност.

=== Анализа по Табела
==== 1. USER

USER (**__user_id__**, **user_name**, **email**, **password**)

{{{user_id → user_name, email, password}}}

- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч.
Додадов UNIQUE ограничување на email затоа што е природен идентификатор.

==== 2. TRANSACTION_ACCOUNT

TRANSACTION_ACCOUNT (__**transaction_account_id**__, account_name, balance, user_id* (USER))

{{{transaction_account_id → account_name, balance, user_id}}}

- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. 

==== 3. TRANSACTION

TRANSACTION (__**transaction_id__**, transaction_name, **amount**, net_amount, **date**)

{{{transaction_id → transaction_name, amount, net_amount, date}}}

Атрибутот {{{net_amount}}} може да се изведе од {{{TRANSACTION_BREAKDOWN}}}. Го задржав намерно заради перформанси, за да избегнам постојано пресметување. За да нема неконзистентност, ќе се одржува преку triggers и апликациска логика.
- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч, со свесен компромис.

==== 4. TRANSACTION_BREAKDOWN

TRANSACTION_BREAKDOWN (__**transaction_breakdown_id__**, transaction_id* (TRANSACTION), transaction_account_id* (TRANSACTION_ACCOUNT), spent_amount, earned_amount)

{{{transaction_breakdown_id → transaction_id, transaction_account_id, spent_amount, earned_amount}}}

- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. 
Го задржав surrogate примарниот клуч за унифициран пристап.

==== 5. TAG

TAG (__**tag_id__**, **tag_name**)

{{{tag_id → tag_name}}}

tag_name → tag_id бидејќи имињата на тагови се уникатни.

- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. 
Го дефинирав UNIQUE (tag_name) за да обезбедам интегритет.

==== 6. TAG_ASSIGNED_TO_TRANSACTION

TAG_ASSIGNED_TO_TRANSACTION(__**tag_assigned_to_transaction_id**__, **tag_id*** (TAG), **transaction_id*** (TRANSACTION))

{{{tag_assigned_to_transaction_id → tag_id, transaction_id}}}

- 1NF: Сите атрибути се атомски, нема повторувачки групи.
- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности.
- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. 
И овде користам surrogate клуч за конзистентност, но истовремено додадов UNIQUE(tag_id, transaction_id) за да спречам дупликати.

**Шемата е во 3NF (практично BCNF) за сите табели. Единствениот свесен исклучок е {{{net_amount}}} во {{{TRANSACTION}}}, кој е додаден заради перформанси.**

https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedDatabaseDevelopment
= Напреден развој на датабаза (Тригери, погледи)

=== Тригери:
- [wiki:Trigger1 Автоматска пресметка на нето износ на трансакција]
- [wiki:Trigger2 Автоматско ажурирање на баланс на трансакциска сметка]
- [wiki:Trigger3 Контрола на баланс со дозволен лимит за тип на акаунт]
- [wiki:Trigger5 Автоматско бришење на трансакции без помошни(breakdown) записи]
- [wiki:Trigger6 Превенција на бришење на акаунти со историја]

- [wiki:Trigger4 Автоматско поставување на датум на трансакција] //Тривијално-може да се постигне со поставување на `DEFAULT` вредност на атрибутот

=== Погледи:
- [wiki:View1 Целсен преглед на трансакции]
- [wiki:View2 Преглед на трансакциски сметки со баланси]
- [wiki:View3 Месечно потрошено по корисник]
- [wiki:View4 Извештај по тагови]
- [wiki:View5 Извештај за дневно трошење]

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger1
= Автоматска пресметка на нето износ на трансакција

==== Опис
Овој тригер служи за автоматска пресметка и ажурирање на нето износот (`net_amount`) на една трансакција врз основа на сите поврзани записи во табелата `transaction_breakdown`

Нето износот се дефинира како `net_amount = SUM(earned_amount - spent_amount)`

Тригерот се активира секогаш кога ќе се:
- додаде нов запис
- ажурира постоечки запис
- избрише запис
од табелата `transaction_breakdown`

Со ова се обезбедува конзистентност на податоците, без потреба апликацијата рачно да ја пресметува вредноста

==== Табели опфатени со тригерот
- transaction
- transaction_breakdown

==== Тип на тригер
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
Активиран над табелата `transaction_breakdown`

==== SQL Код
{{{#!sql
CREATE OR REPLACE FUNCTION update_transaction_net_amount()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE transaction t
    SET net_amount = (
        SELECT COALESCE(SUM(tb.earned_amount - tb.spent_amount), 0)
        FROM transaction_breakdown tb
        WHERE tb.transaction_id = t.transaction_id
    )
    WHERE t.transaction_id = COALESCE(NEW.transaction_id, OLD.transaction_id);

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_transaction_net_amount
AFTER INSERT OR UPDATE OR DELETE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION update_transaction_net_amount();
}}}

==== Објаснување на логиката

При секоја промена во `transaction_breakdown`, тригерот:
- Го пронаоѓа поврзаниот `transaction_id`
- Ги сумира сите `earned_amount` и `spent_amount`
- Ја ажурира колоната `net_amount` во табелата `transaction`

Се користи `COALESCE` за да се избегне `NULL` вредност кога нема breakdown записи

==== Причина за користење
Овој тригер е неопходен бидејќи:
- `net_amount` е деривиран атрибут
- не смее да зависи од апликативна логика

спречува:
- неконзистентни податоци
- човечки грешки
- дуплирање на бизнис логика

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger2
= Автоматско ажурирање на баланс на трансакциска сметка

==== Опис
Овој тригер овозможува автоматско ажурирање на балансот (`balance`) на трансакциските сметки при секоја промена во табелата `transaction_breakdown`

Секој запис во `transaction_breakdown` претставува:
- приход (`earned_amount`)
- трошење (`spent_amount`)

Тригерот гарантира дека балансот на сметката секогаш ја рефлектира реалната состојба по секоја трансакција

==== Табели опфатени со тригерот
- transaction_account
- transaction_breakdown

==== Тип на тригер
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
Активиран над табелата `transaction_breakdown`

==== SQL Код
{{{#!sql
CREATE OR REPLACE FUNCTION update_transaction_account_balance()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- При бришење на запис (rollback на ефектот)
    IF TG_OP = 'DELETE' THEN
        UPDATE transaction_account
        SET balance = balance - OLD.earned_amount + OLD.spent_amount
        WHERE transaction_account_id = OLD.transaction_account_id;

    -- При додавање на нов запис
    ELSIF TG_OP = 'INSERT' THEN
        UPDATE transaction_account
        SET balance = balance + NEW.earned_amount - NEW.spent_amount
        WHERE transaction_account_id = NEW.transaction_account_id;

    -- При ажурирање на постоечки запис
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE transaction_account
        SET balance = balance
            - OLD.earned_amount + OLD.spent_amount
            + NEW.earned_amount - NEW.spent_amount
        WHERE transaction_account_id = NEW.transaction_account_id;
    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_transaction_account_balance
AFTER INSERT OR UPDATE OR DELETE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION update_transaction_account_balance();
}}}

==== Објаснување на логиката
`DELETE`:
- Се поништува ефектот од избришаниот запис

`INSERT`:
- Приходите се додаваат на балансот
- Трошоците се одземаат од балансот

`UPDATE`:
- Прво се поништува стариот ефект
- Потоа се применува новиот ефект


==== Причина за користење
Овој тригер е клучен затоа што:
- `balance` е динамичка и критична вредност
- рачно ажурирање е ризично
- апликацијата не смее да биде единствен извор на вистина
- базата ја контролира финансиската логика
- се елиминираат грешки при ажурирање и бришење

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger3
= Контрола на баланс со дозволен лимит за тип на акаунт

==== Опис

Овој тригер овозможува напредна валидација на трансакции со цел да се спречи надминување на дозволениот лимит на трансакциска сметка, при што:
- се поддржува дозволен лимит и наместо фиксна граница (0), тригерот користи параметаризиран лимит, кој се дефинира при креирање на тригерот
- се обработува edge case кога при `UPDATE` се менува трансакцискиот акаунт
- се применува различна логика во зависност од типот на акаунт
- типот на акаунтот не е експлицитен атрибут, туку се утврдува преку името на акаунтот, кое мора да го содржи типот (пример: Cash Wallet, Credit Card VISA, Forex EUR Account)

Тригерот се извршува само ако името на акаунтот го содржи типот проследен како аргумент пред внесување или ажурирање на помошна трансакција

Пример:
- кеш сметки → лимит 0
- кредитни сметки → лимит -10000

==== Табели опфатени со тригерот
- `transaction_account`
- `transaction_breakdown`

==== Тип на тригер
- **BEFORE INSERT
- **BEFORE UPDATE
Активиран над табелата `transaction_breakdown`

==== Поддржани типови на акаунти
|| //Тип на акаунт || //Пример име || //Логика ||
|| **CASH || Cash, Cash Wallet, Cash MKD || Не е дозволен негативен баланс ||
|| **DEBIT || Debit Account || Не е дозволен негативен баланс ||
|| **CREDIT || Credit Card VISA, Credit Card Gold || Дозволен негативен баланс до дефиниран лимит ||
|| **FOREX || Forex EUR, FX USD || Дозволен негативен баланс до дефиниран лимит ||

==== Параметри на тригерот
|| //Параметар || //Опис ||
|| **`TG_ARGV[0]` || Тип на акаунт (пример: cash, credit, debit, forex) ||
|| **`TG_ARGV[1]` || Дозволен минимален баланс (лимит) ||

==== SQL код
{{{#!sql
CREATE OR REPLACE FUNCTION enforce_account_limit_by_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    allowed_type TEXT;
    allowed_limit NUMERIC;

    old_balance NUMERIC;
    new_balance NUMERIC;

    old_account_name TEXT;
    new_account_name TEXT;
BEGIN
    allowed_type  := LOWER(TG_ARGV[0]);
    allowed_limit := TG_ARGV[1]::NUMERIC;

    -- ===============================
    -- INSERT
    -- ===============================
    IF TG_OP = 'INSERT' THEN
        SELECT account_name, balance
        INTO new_account_name, new_balance
        FROM transaction_account
        WHERE transaction_account_id = NEW.transaction_account_id;

        IF LOWER(new_account_name) LIKE '%' || allowed_type || '%' THEN
            new_balance := new_balance
                           + COALESCE(NEW.earned_amount, 0)
                           - COALESCE(NEW.spent_amount, 0);

            IF new_balance < allowed_limit THEN
                RAISE EXCEPTION
                    'The limit for the % account has been exceeded! Balance: %, limit: %',
                    allowed_type, new_balance, allowed_limit;
            END IF;
        END IF;

        RETURN NEW;
    END IF;

    -- ===============================
    -- UPDATE
    -- ===============================
    IF TG_OP = 'UPDATE' THEN

        -- Читање на стар и нов акаунт
        SELECT account_name, balance
        INTO old_account_name, old_balance
        FROM transaction_account
        WHERE transaction_account_id = OLD.transaction_account_id;

        SELECT account_name, balance
        INTO new_account_name, new_balance
        FROM transaction_account
        WHERE transaction_account_id = NEW.transaction_account_id;

        -- Проверка на стариот акаунт (rollback ефект)
        IF LOWER(old_account_name) LIKE '%' || allowed_type || '%' THEN
            old_balance := old_balance
                           - COALESCE(OLD.earned_amount, 0)
                           + COALESCE(OLD.spent_amount, 0);

            IF old_balance < allowed_limit THEN
                RAISE EXCEPTION
                    'The limit for the % account has been exceeded during rollback! Balance: %, limit: %',
                    allowed_type, old_balance, allowed_limit;
            END IF;
        END IF;

        -- Проверка на новиот акаунт (apply нов ефект)
        IF LOWER(new_account_name) LIKE '%' || allowed_type || '%' THEN
            new_balance := new_balance
                           + COALESCE(NEW.earned_amount, 0)
                           - COALESCE(NEW.spent_amount, 0);

            IF new_balance < allowed_limit THEN
                RAISE EXCEPTION
                    'The limit for the % account has been exceeded during updation! Balance: %, limit: %',
                    allowed_type, new_balance, allowed_limit;
            END IF;
        END IF;

        RETURN NEW;
    END IF;

    RETURN NEW;
END;
$$;
}}}

===== Креирање на тригерите:

- Кеш акаунти (без дозволен минус)
{{{#!sql
CREATE TRIGGER trg_cash_account_limit
BEFORE INSERT OR UPDATE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION enforce_account_limit_by_name('cash', 0);
}}}

- Кредитни акаунти (дозволен минус до -10000)
{{{#!sql
CREATE TRIGGER trg_credit_account_limit
BEFORE INSERT OR UPDATE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION enforce_account_limit_by_name('credit', -10000);
}}}

- Дебитни акаунти (без дозволен минус)
{{{#!sql
CREATE TRIGGER trg_debit_account_limit
BEFORE INSERT OR UPDATE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION enforce_account_limit_by_name('debit', 0);
}}}

- Forex акаунти (дозволен минус до -1000)
{{{#!sql
CREATE TRIGGER trg_forex_account_limit
BEFORE INSERT OR UPDATE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION enforce_account_limit_by_name('forex', -1000);
}}}

==== Објаснување на логиката

Типот на акаунтот се утврдува со пребарување во `account_name`, се користи `LIKE %type%` за флексибилност

При `UPDATE`:
- се проверува стариот акаунт (rollback)
- потоа новиот акаунт (apply)

Лимитот се применува само ако типот се совпаѓа

Дозволениот лимит се проследува како аргумент на тригерот, функцијата ја чита вредноста преку `TG_ARGV`, балансот се пресметува пред реалното запишување, доколку се надмине лимитот → трансакцијата се блокира

Овој пристап овозможува флексибилна и повторно употреблива логика

==== Причина за користење
- Поддршка за различни типови сметки
- Централизирана контрола на бизнис логика
- Избегнување "hardcoded" вредности
- Лесна промена без измена на кодот

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger5
= Автоматско бришење на трансакции без помошни(breakdown) записи

==== Опис
Овој тригер автоматски ја брише трансакцијата доколку по бришење на запис од `transaction_breakdown` повеќе не постојат поврзани `breakdown` записи за таа трансакција

Со ова се обезбедува дека не постојат „празни“ трансакции кои немаат финансиско значење

==== Табели опфатени со тригерот
- `transaction`
- `transaction_breakdown`

==== Тип на тригер
- **AFTER DELETE
Активиран над табелата `transaction_breakdown`

==== SQL код
{{{#!sql
CREATE OR REPLACE FUNCTION delete_empty_transaction()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Проверка дали постојат други breakdown записи за истата трансакција
    IF NOT EXISTS (
        SELECT 1
        FROM transaction_breakdown
        WHERE transaction_id = OLD.transaction_id
    ) THEN
        DELETE FROM transaction
        WHERE transaction_id = OLD.transaction_id;
    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_delete_empty_transaction
AFTER DELETE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION delete_empty_transaction();
}}}

==== Објаснување на логиката
По бришење на breakdown запис:
- се проверува дали трансакцијата има други поврзани записи
Доколку нема:
    - трансакцијата се смета за невалидна
    - автоматски се брише
Ова однесување ја моделира композицијата помеѓу `transaction` и `transaction_breakdown`

==== Причина за користење

Овој тригер е важен затоа што:
- трансакција без breakdown нема значење
- спречува акумулација на некорисни податоци
- ја одржува базата чиста и логична
Оваа логика не може секогаш безбедно да се остави на апликацијата

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger6
= Превенција на бришење на акаунти со историја

==== Опис
Овој тригер спречува бришење на трансакциска сметка доколку таа има поврзани записи во табелата `transaction_breakdown`

Со ова се заштитува финансиската историја и се спречува губење на важни податоци

==== Табели опфатени со тригерот
- `transaction_account`
- `transaction_breakdown`

==== Тип на тригер
- **BEFORE DELETE
Активиран над табелата `transaction_account`

==== SQL код
{{{#!sql
CREATE OR REPLACE FUNCTION prevent_account_delete_with_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM transaction_breakdown
        WHERE transaction_account_id = OLD.transaction_account_id
    ) THEN
        RAISE EXCEPTION
            'The transaction account cannot be deleted because it contains history of transactions, delete them first!';
    END IF;

    RETURN OLD;
END;
$$;


CREATE TRIGGER trg_prevent_account_delete_with_history
BEFORE DELETE
ON transaction_account
FOR EACH ROW
EXECUTE FUNCTION prevent_account_delete_with_history();
}}}

==== Објаснување на логиката
Пред бришење на сметката:
- се проверува дали постојат поврзани breakdown записи
Доколку постојат:
- бришењето се прекинува
- се прикажува соодветна грешка
Ова обезбедува зачувување на историските податоци

==== Причина за користење
Овој тригер е важен бидејќи:
- финансиските податоци не смеат да се губат
- бришење на сметка може да доведе до неконзистентни извештаи, сите извештаи остануваат точни и доверливи
- `ON DELETE CASCADE` не е соодветен за ваков случај:
`ON DELETE CASCADE` автоматски ги брише сите поврзани записи во зависните табели кога ќе се избрише родителскиот запис

Иако ова е корисно во одредени сценарија, во финансиски системи како FEiN тоа претставува сериозен ризик за губење на финансиската историја, која никогаш не смее автоматски да се избрише

Сметката можеби повеќе не е активна, но историјата останува, а `ON DELETE CASCADE` имплицира дека:
- ако сметката не постои → трансакциите не постоеле, што е бизнис логички неточно

https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger4
= Автоматско поставување на датум на трансакција

==== Опис
Овој тригер автоматски го поставува датумот на трансакцијата (`date`) доколку при внесување на трансакција истиот не е зададен, базата на податоци ќе го користи тековниот системски датум и време

Со ова се обезбедува дека секоја трансакција има валиден временски запис

==== Табели опфатени со тригерот
- `transaction`

==== Тип на тригер
- **BEFORE INSERT
Активиран над табелата `transaction`

==== SQL код
{{{#!sql
CREATE OR REPLACE FUNCTION set_transaction_date()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.date IS NULL THEN
        NEW.date := NOW();
    END IF;

    RETURN NEW;
END;
$$;


CREATE TRIGGER trg_set_transaction_date
BEFORE INSERT
ON transaction
FOR EACH ROW
EXECUTE FUNCTION set_transaction_date();
}}}

==== Објаснување на логиката
Пред внесување на нова трансакција:
- се проверува дали колоната `date` е празна
- доколку е `NULL`, се поставува тековниот датум и време

==== Причина за користење
Овој тригер е важен затоа што:
- овозможува стандардизирано однесување при внесување податоци
- времето е клучен атрибут за финансиски податоци
- апликацијата не секогаш е сигурен извор
- се елиминираат празни или невалидни датуми
Исто така:
- го поедноставува API-то
- ја намалува комплексноста на frontend-логиката

https://develop.finki.ukim.mk/projects/FEIN/wiki/View1
= Целосен преглед на трансакции

==== Опис
Овој поглед обезбедува целосен преглед на трансакциите, комбинирајќи податоци од повеќе табели во еден приказ

Погледот е наменет за:
- прикажување трансакции во кориснички интерфејс
- извештаи
- аналитички пребарувања

==== Табели опфатени со погледот
- `transaction`
- `transaction_breakdown`
- `transaction_account`
- `tag`
- `tag_assigned_to_transaction`

==== SQL код
{{{#!sql
CREATE OR REPLACE VIEW vw_transaction_overview AS
SELECT
    t.transaction_id,
    t.transaction_name,
    t.date,
    t.net_amount,

    ta.transaction_account_id,
    ta.account_name,

    STRING_AGG(DISTINCT tg.tag_name, ', ') AS tags

FROM transaction t
JOIN transaction_breakdown tb
    ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
    ON ta.transaction_account_id = tb.transaction_account_id
LEFT JOIN tag_assigned_to_transaction tat
    ON tat.transaction_id = t.transaction_id
LEFT JOIN tag tg
    ON tg.tag_id = tat.tag_id

GROUP BY
    t.transaction_id,
    t.transaction_name,
    t.date,
    t.net_amount,
    ta.transaction_account_id,
    ta.account_name;
}}}

==== Објаснување на логиката
- Се комбинираат основните податоци за трансакцијата
- Се прикажува сметката на која трансакцијата влијае
- Таговите се агрегираат во една текстуална колона со `STRING_AGG`
- `LEFT JOIN` се користи за тагови бидејќи тие се опционални

==== Причина за користење
Овој поглед е неопходен бидејќи:
- ја централизира логиката за читање на трансакции
- ја намалува комплексноста во апликацијата
- обезбедува конзистентен формат за приказ на податоци
Без ваков поглед, секој извештај би морал повторно да ги имплементира истите `JOIN`-ови
https://develop.finki.ukim.mk/projects/FEIN/wiki/View2
= Преглед на трансакциски сметки со баланси

==== Опис
Овој поглед обезбедува преглед на сите трансакциски сметки, заедно со нивниот тековен баланс и агрегирани финансиски податоци

Погледот е наменет за:
- dashboard приказ
- брз преглед на финансиска состојба
- извештаи по сметка

==== Табели опфатени со погледот
- `transaction_account`

==== SQL код
{{{#!sql
CREATE OR REPLACE VIEW vw_transaction_account_summary AS
SELECT
    transaction_account_id,
    account_name,
    balance
FROM transaction_account
GROUP BY
    transaction_account_id,
    account_name,
    balance;
}}}

==== Објаснување на логиката
Брз и краток поглед кој ги зема полињата од `transaction_account`

==== Причина за користење
Овој поглед е важен затоа што:
- balance покажува моментална состојба
- сите релевантни податоци за сметка се достапни во една структура

https://develop.finki.ukim.mk/projects/FEIN/wiki/View3
= Месечно потрошено по корисник

==== Опис
Овој поглед обезбедува преглед на месечната потрошувачка за секој корисник, каде податоците се групираат по корисник и месец(придружен со година)

Погледот е наменет за:
- месечни финансиски извештаи
- тренд анализи
- визуелизации (графици)

==== Табели опфатени со погледот
- `transaction`
- `transaction_breakdown`
- `transaction_account`

==== SQL код
{{{#!sql
CREATE OR REPLACE VIEW vw_monthly_spending_by_user AS
SELECT
    ta.user_id,
    EXTRACT(MONTH FROM t.date) AS month,
    EXTRACT(YEAR FROM t.date) AS year,    

    COALESCE(SUM(tb.spent_amount), 0) AS total_spent

FROM transaction t
JOIN transaction_breakdown tb
    ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
    ON ta.transaction_account_id = tb.transaction_account_id

GROUP BY
    ta.user_id,
    EXTRACT(YEAR FROM t.date),
    EXTRACT(MONTH FROM t.date);
}}}

==== Објаснување на логиката
- Се користи датумот на трансакцијата како временска референца
- Се агрегира само `spent_amount`
- Податоците се групираат по корисник и по месец
Ова овозможува јасен временски преглед на трошоците

==== Причина за користење
Овој поглед е важен затоа што:
- месечните извештаи се честa потреба
- сложените агрегации се централизирани
- апликацијата не мора да имплементира временска логика

https://develop.finki.ukim.mk/projects/FEIN/wiki/View4
= Извештај по тагови

==== Опис
Овој поглед обезбедува преглед на потрошувачката по тагови, со што се овозможува категоризација и анализа на трошоците според нивната намена (храна, транспорт, сметки, забава и сл.)

Погледот ги поврзува трансакциите со нивните тагови и ја сумира вкупната потрошувачка за секој таг

==== Табели опфатени со погледот
- `tag`
- `tag_assigned_to_transaction`
- `transaction`
- `transaction_breakdown`

==== SQL код
{{{#!sql
CREATE OR REPLACE VIEW vw_spending_by_tag AS
SELECT
    tg.tag_id,
    tg.tag_name,

    COALESCE(SUM(tb.spent_amount), 0) AS total_spent,
    COUNT(DISTINCT t.transaction_id) AS transaction_count

FROM tag tg
JOIN tag_assigned_to_transaction tat
    ON tat.tag_id = tg.tag_id
JOIN transaction t
    ON t.transaction_id = tat.transaction_id
JOIN transaction_breakdown tb
    ON tb.transaction_id = t.transaction_id

GROUP BY
    tg.tag_id,
    tg.tag_name;
}}}

==== Објаснување на логиката
- Се земаат само трансакции кои имаат доделен таг
- `spent_amount` се агрегира по таг
- `COUNT(DISTINCT ...)` се користи за да се избегне дуплирање на трансакции

==== Причина за користење
- категоризацијата е суштинска за лични финансии
- овозможува анализа на трошоци по намена
- ја елиминира потребата од сложени `JOIN` и `GROUP BY` барања

https://develop.finki.ukim.mk/projects/FEIN/wiki/View5
= Извештај за дневно трошење

==== Опис
Овој поглед обезбедува преглед на дневната потрошувачка за секој корисник, каде податоците се групираат по корисник и датум

Погледот е наменет за:
- дневни извештаи
- анализа на навики
- пресметка на просечна потрошувачка

==== Табели опфатени со view-от
- `transaction`
- `transaction_breakdown`
- `transaction_account`

==== SQL код
{{{#!sql
CREATE OR REPLACE VIEW vw_daily_spending_by_user AS
SELECT
    ta.user_id,
    DATE(t.date) AS spending_date,

    COALESCE(SUM(tb.spent_amount), 0) AS total_spent

FROM transaction t
JOIN transaction_breakdown tb
    ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
    ON ta.transaction_account_id = tb.transaction_account_id

GROUP BY
    ta.user_id,
    DATE(t.date);
}}}

==== Објаснување на логиката
- Се користи само датумскиот дел од `timestamp`
- Се агрегира исклучиво `spent_amount`
- Групирањето по корисник овозможува персонализирани анализи

==== Причина за користење
- дневната анализа дава најпрецизен увид во навиките
- е основа за пресметка на просеци и лимити
- ги поедноставува временските пресметки

https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedApplicationDevelopment
= Напреден апликативен развој (Трансакции, Pooling)

За да се разликуваат трансакциите во системот како ентитети и трансакциите како теоретски поим во областа на датабазите, првите ќе ги нарекувам "финансиски трансакции", а вторите "трансакции во датабаза" во понатамошните спомнувања

=== Pooling на конекции
Го користам пакетот `postgres.js` (инстанциран во [source:/app/lib/db.ts]), кој автоматски воспоставува pooling. Без потреба од дополнителни конфигурации, овој систем одржува активни конекции(стандардно 10 паралелни конекции) што драстично ја намалува латенцијата помеѓу серверските акции во Next.js и операциите на базата на податоци.

Овој систем е заштитен од дуплирање на конекции при hot-reload во развојна околина, со зачувување на инстанцата во `global` објектот.

=== Трансакции во датабаза
Користам `sql.begin()` блокови за секоја операција што бара прво проверка или повеќекратен упис, елиминирајќи го ризикот од race conditions. 

'''Пример 1: Внесување на финансиска трансакција''' 

Сместена во [source:/app/(app)/add/actions.ts#L205:292], акцијата `addTransaction` ги извршува следните чекори како една атомична целина:
 1. Впишува основен запис во табелата `transaction`.
 2. Впишува релациони записи во `transaction_breakdown`.
 3. Го ажурира балансот на соодветната сметка во `transaction_account`.
 4. Селектира постојни или внесува нови тагови во `tag`.
 5. Гради поврзување во табелата `tag_assigned_to_transaction`.

'''Пример 2: Валидација и манипулација врз основа на проверка'''

За да се осигураме дека нема да се зачуваат дупликати поради истовремени кликови или барања(race conditions), се користи истата `sql.begin()` логика при креирање и промена на профили:
- '''Регистрација на профил:''' Во [source:/app/(auth)/actions.ts#L68:79] проверката дали внесениот `email` веќе постои се извршува во иста трансакција со креирањето на новиот профил. Доколку друга регистрација се обиде со истиот мејл во тој момент, трансакцијата се блокира.
- '''Ажурирање на профил:''' При промена на email во [source:/app/(app)/profile/actions.ts#L35:51], сите проверки за преклопување и самата `UPDATE` команда се спроведуват атомично.
- '''Додавање тагови:''' Во [source:app/(app)/add/actions.ts#L80:90], акцијата за додавање нов таг користи трансакција за првично да изврши `SELECT` и да провери дали постои таг со тоа име, пред да направи `INSERT`.

'''Заштитен механизам:''' Ако било кој од чекорите во блоковите врати грешка(на пример: тагот веќе постои, балансот е невалиден или податоците се некомплетни), целиот `sql.begin` блок прави '''rollback''' и извршувањето се прекинува, што значи дека ниту еден делумен податок не останува трајно зачуван во базата.

https://develop.finki.ukim.mk/projects/FEIN/wiki/OtherTopics
= Останати теми: Безбедност, перформанси и одржување на базата

=== 1. Безбедност на ниво на база
Имплементирани се следните безбедносни механизми директно при комуникацијата со базата, со што се намалува нападниот вектор независно од самиот NextJS сервер:
 - '''Заштита од SQL вбризгување(SQL injection):''' Клиентската библиотека `postgres.js` користи параметризирани прашалници преку Tagged Template Literals(на пример `sql\`...\``) по дизајн. Ова спречува директно извршување на малициозен SQL код преку корисничките влезни параметри, бидејќи влезот е секогаш парсиран како податок, а не како команда.
 - '''Енкриптирана комуникација(SSL/TLS):''' Како што е наведено во конфигурацискиот фајл([source:/app/lib/db.ts#L11]), конекцијата кон базата строго наметнува SSL сертификат преку параметарот `ssl: 'require'`. Ова ја штити транзицијата на чувствителните податоци и лозинки од "man-in-the-middle" напади.
 - '''Безбедно поврзување со база:''' Конекциските стрингови никогаш не се чуваат во изворниот код. Тие се изолирани преку заштитени околински променливи (`POSTGRES_URL`).

=== 2. Перформанси и оптимизација
Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата.

Првично тестирање беше направено со мал број редови во табелите:

[wiki:SmallDatabasePerformance Перформанси со мала датабаза]

Во оваа состојба PostgreSQL користеше `Seq Scan` и по додавање на индексите. Тоа е очекувано однесување, бидејќи за табели со многу мал број редови целосното читање на табелата е поевтино од пристап преку индекс.

Поради тоа, за пореална анализа беше генериран поголем сет на тест-податоци, при што податоците се логички поврзани преку постоечките релации во базата.

По генерирање на тест-податоците, базата ја има следната состојба:

||= Табела =||= Број на редови =||
|| `user` || 103 ||
|| `transaction` || 100012 ||
|| `transaction_account` || 505 ||
|| `transaction_breakdown` || 110011 ||
|| `tag` || 31 ||
|| `tag_assigned_to_transaction` || 125016 ||

Оваа количина на податоци е доволна за PostgreSQL optimizer-от да започне да прави различни планови за извршување и да користи дел од предложените индекси кога тие се поисплатливи од `Seq Scan`.

==== Предложени дополнителни индекси ====

Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи:

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови.

==== Сценарио 1: Трендови на трошење според тагови за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
tg.tag_name,
DATE_TRUNC('month', t.date) AS month,
SUM(tb.spent_amount) AS total_spent
FROM tag_assigned_to_transaction tat
JOIN "transaction" t
ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb
ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg
ON tat.tag_id = tg.tag_id
WHERE
ta.user_id = 5
AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY
tg.tag_name, month
ORDER BY
tg.tag_name, month;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=4898.54..4900.99 rows=98 width=52) (actual time=51.593..51.622 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=4383
->  Sort  (cost=4898.54..4898.79 rows=98 width=26) (actual time=51.574..51.582 rows=81 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 28kB
->  Hash Join  (cost=2498.81..4895.30 rows=98 width=26) (actual time=28.248..51.522 rows=81 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=2497.11..4893.07 rows=98 width=18) (actual time=28.204..51.436 rows=81 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
(actual time=0.014..9.236 rows=125016 loops=1)
->  Hash
->  Nested Loop
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.012..8.429 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 5)
Rows Removed by Filter: 500
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 1.392 ms
Execution Time: 51.744 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2490.16..2492.59 rows=97 width=52) (actual time=6.936..6.963 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=3273 read=76
->  Sort  (cost=2490.16..2490.40 rows=97 width=26) (actual time=6.921..6.928 rows=81 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=16.95..2486.96 rows=97 width=26) (actual time=0.155..6.835 rows=81 loops=1)
->  Nested Loop  (cost=16.80..2480.67 rows=97 width=18) (actual time=0.135..6.624 rows=81 loops=1)
->  Nested Loop  (cost=16.38..2443.39 rows=77 width=22) (actual time=0.104..5.104 rows=81 loops=1)
->  Nested Loop  (cost=16.09..2050.95 rows=1089 width=10) (actual time=0.087..0.458 rows=1000 loops=1)
->  Bitmap Heap Scan on transaction_account ta
Recheck Cond: (user_id = 5)
->  Bitmap Index Scan on idx_transaction_account_user_id
Index Cond: (user_id = 5)
->  Bitmap Heap Scan on transaction_breakdown tb
Recheck Cond: (transaction_account_id = ta.transaction_account_id)
->  Bitmap Index Scan on idx_transaction_breakdown_account_id
Index Cond: (transaction_account_id = ta.transaction_account_id)
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Index Scan using idx_tag_assigned_transaction_id on tag_assigned_to_transaction tat
Index Cond: (transaction_id = tb.transaction_id)
->  Memoize
Cache Key: tat.tag_id
->  Index Scan using tag_pkey on tag tg
Index Cond: (tag_id = tat.tag_id)
Planning Time: 1.738 ms
Execution Time: 7.055 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 1.392 ms || 1.738 ms ||
|| Execution Time || 51.744 ms || 7.055 ms ||
|| Подобрување на Execution Time || / || ~86.36% ||
|| Забрзување || / || ~7.33x ||
|| Тип на скенирање пред индекси || `Seq Scan` на големите табели || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL експлицитно ги користи:

* `idx_transaction_account_user_id` преку `Bitmap Index Scan`
* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan`
* `idx_tag_assigned_transaction_id` преку `Index Scan`

Индексот `idx_transaction_date` не е директно искористен во овој план, затоа што optimizer-от прво ги ограничува податоците преку корисничките сметки и breakdown записите, а потоа пристапува до трансакциите преку примарниот клуч `transaction_pkey`.

'''Заклучок:'''

Ова сценарио има најголемо подобрување. Времето на извршување се намали од 51.744 ms на 7.055 ms, што е приближно 86.36% подобрување, односно околу 7.33 пати побрзо извршување. Причината е што по додавање на индексите PostgreSQL повеќе не мора секвенцијално да ги чита `transaction_account`, `transaction_breakdown` и `tag_assigned_to_transaction`, туку директно ги лоцира релевантните записи преку индексите. Ова покажува дека индексите се корисни за извештаи кои филтрираат податоци за конкретен корисник и потоа ги поврзуваат трансакциите со тагови.

==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ====

'''Цел:'''
Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
COALESCE(SUM(tb.spent_amount), 0) AS total_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
total_spent DESC
LIMIT 10;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
Limit  (cost=4969.28..4969.31 rows=10 width=49) (actual time=73.712..73.717 rows=10 loops=1)
Buffers: shared hit=1540
->  Sort  (cost=4969.28..4970.54 rows=505 width=49) (actual time=73.711..73.714 rows=10 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: top-N heapsort  Memory: 26kB
->  HashAggregate  (cost=4952.06..4958.37 rows=505 width=49) (actual time=73.611..73.658 rows=113 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2756.94..4883.26 rows=13760 width=23) (actual time=34.771..69.224 rows=13568 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=2740.57..4830.47 rows=13760 width=10) (actual time=34.564..66.184 rows=13568 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.012..8.780 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
Rows Removed by Filter: 87672
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.538 ms
Execution Time: 73.792 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
Limit  (cost=3675.91..3675.93 rows=10 width=49) (actual time=47.820..47.826 rows=10 loops=1)
Buffers: shared hit=1540 read=36
->  Sort  (cost=3675.91..3677.17 rows=505 width=49) (actual time=47.818..47.822 rows=10 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: top-N heapsort  Memory: 26kB
->  HashAggregate  (cost=3658.68..3665.00 rows=505 width=49) (actual time=47.717..47.765 rows=113 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=1463.44..3589.80 rows=13776 width=23) (actual time=9.049..43.449 rows=13568 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1447.08..3536.98 rows=13776 width=10) (actual time=8.845..40.394 rows=13568 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.011..9.164 rows=110011 loops=1)
->  Hash
->  Bitmap Heap Scan on transaction t
Recheck Cond: (date >= (now() - '1 year'::interval))
Heap Blocks: exact=834
->  Bitmap Index Scan on idx_transaction_date
Index Cond: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.654 ms
Execution Time: 47.902 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.538 ms || 0.654 ms ||
|| Execution Time || 73.792 ms || 47.902 ms ||
|| Подобрување на Execution Time || / || ~35.08% ||
|| Забрзување || / || ~1.54x ||
|| Тип на скенирање пред индекси || `Seq Scan` на `transaction` || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan` + `Bitmap Heap Scan` на `transaction` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL го користи `idx_transaction_date` преку:

{{{#!sql
Bitmap Index Scan on idx_transaction_date
Index Cond: (date >= (now() - '1 year'::interval))
}}}

Останатите табели, како `transaction_breakdown` и `transaction_account`, сè уште се читаат со `Seq Scan`, затоа што query-то пресметува агрегат за сите сметки, а не за еден конкретен корисник или една конкретна сметка.

'''Заклучок:'''

Времето на извршување се намали од 73.792 ms на 47.902 ms, што претставува приближно 35.08% подобрување. Ова подобрување е директно поврзано со индексот `idx_transaction_date`, бидејќи извештајот филтрира трансакции само од последната година. Пред индексот, PostgreSQL мораше да ја чита целата табела `transaction` и да отфрли 87672 редови преку filter. По додавање на индексот, PostgreSQL користи `Bitmap Index Scan` за побрзо да ги најде трансакциите од последната година.

==== Сценарио 3: Годишни трендови на трансакции за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`.

Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
ta.user_id = 5
AND t.date >= DATE_TRUNC('year', CURRENT_DATE)
AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
ta.account_name;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2511.77..2514.21 rows=5 width=145) (actual time=26.493..26.495 rows=1 loops=1)
Group Key: ta.account_name
Buffers: shared hit=3706
->  Sort  (cost=2511.77..2511.93 rows=67 width=31) (actual time=26.366..26.372 rows=70 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=11.67..2509.73 rows=67 width=31) (actual time=0.322..26.308 rows=70 loops=1)
->  Hash Join  (cost=11.38..2103.69 rows=1089 width=27) (actual time=0.301..21.727 rows=1000 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.019..8.175 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 5)
Rows Removed by Filter: 500
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
Rows Removed by Filter: 1
Planning Time: 0.495 ms
Execution Time: 26.571 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2458.99..2461.40 rows=5 width=145) (actual time=5.086..5.088 rows=1 loops=1)
Group Key: ta.account_name
Buffers: shared hit=3023
->  Sort  (cost=2458.99..2459.16 rows=66 width=31) (actual time=4.954..4.960 rows=70 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=16.38..2457.00 rows=66 width=31) (actual time=0.104..4.917 rows=70 loops=1)
->  Nested Loop  (cost=16.09..2050.95 rows=1089 width=27) (actual time=0.086..0.420 rows=1000 loops=1)
->  Bitmap Heap Scan on transaction_account ta
Recheck Cond: (user_id = 5)
->  Bitmap Index Scan on idx_transaction_account_user_id
Index Cond: (user_id = 5)
->  Bitmap Heap Scan on transaction_breakdown tb
Recheck Cond: (transaction_account_id = ta.transaction_account_id)
->  Bitmap Index Scan on idx_transaction_breakdown_account_id
Index Cond: (transaction_account_id = ta.transaction_account_id)
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
Rows Removed by Filter: 1
Planning Time: 0.724 ms
Execution Time: 5.169 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.495 ms || 0.724 ms ||
|| Execution Time || 26.571 ms || 5.169 ms ||
|| Подобрување на Execution Time || / || ~80.55% ||
|| Забрзување || / || ~5.14x ||
|| Тип на скенирање пред индекси || `Seq Scan` на `transaction_breakdown` и `transaction_account` || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL ги користи:

* `idx_transaction_account_user_id` преку `Bitmap Index Scan`
* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan`

Индексот `idx_transaction_date` не е директно искористен во овој план. Причината е што optimizer-от прво ги ограничува податоците преку конкретниот `user_id` и неговите сметки, а потоа пристапува до трансакциите преку `transaction_pkey`. Во овој случај филтрирањето по корисник е поселективно од самото филтрирање по датум.

'''Заклучок:'''

Времето на извршување се намали од 26.571 ms на 5.169 ms, што претставува приближно 80.55% подобрување, односно околу 5.14 пати побрзо извршување. Ова подобрување е резултат на тоа што PostgreSQL повеќе не ја чита целата табела `transaction_breakdown` и целата табела `transaction_account`, туку директно ги наоѓа сметките на конкретниот корисник и breakdown записите поврзани со тие сметки.

==== Финален заклучок од анализата ====

Првичната анализа со мал број податоци не покажа реална употреба на индексите, затоа што табелите беа премногу мали и PostgreSQL правилно избра `Seq Scan`. Поради тоа беше генериран поголем, логички поврзан сет на тест-податоци, кој содржи 100012 трансакции, 110011 breakdown записи и 125016 записи за доделени тагови.

По зголемување на количината на податоци, индексите почнаа да имаат значајно влијание врз execution plan-от и времето на извршување.

||= Сценарио =||= Execution Time пред индекси =||= Execution Time по индекси =||= Подобрување =||= Индекси искористени =||
|| Трендови на трошење според тагови за корисник || 51.744 ms || 7.055 ms || ~86.36% || Да ||
|| Сметки со највисоко трошење во изминатата година || 73.792 ms || 47.902 ms || ~35.08% || Да ||
|| Годишни трендови на трансакции за корисник || 26.571 ms || 5.169 ms || ~80.55% || Да ||

Најголемо подобрување се забележува кај извештаите кои филтрираат по конкретен корисник, затоа што индексите `idx_transaction_account_user_id` и `idx_transaction_breakdown_account_id` овозможуваат PostgreSQL директно да ги најде релевантните сметки и breakdown записи, наместо да ги чита целите табели.

Кај извештајот за сметки со највисоко трошење во последната година, најважен е индексот `idx_transaction_date`, затоа што условот по датум ја намалува количината на трансакции кои треба да се обработат.

Заклучокот е дека предложените индекси се оправдани и корисни за FEiN, особено кога базата содржи поголем број трансакции. Тие значително го намалуваат времето на извршување на аналитичките извештаи и ја подобруваат скалабилноста на системот.





=== 3. Интегритет и конзистентност
Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (`CONSTRAINTS`) и тригери кои ја заштитуваат финансиската историја на корисниците.

{{{#!sql
ALTER TABLE "user"
ADD CONSTRAINT user_email_unique UNIQUE (email);
}}}

- '''Надворешни клучеви:''' Релациите помеѓу табелите се реализирани преку надворешни клучеви (`FOREIGN KEY`). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, трансакции или тагови.
- '''Заштита на финансиска историја:''' За разлика од автоматско каскадно бришење, кај трансакциските сметки се користи тригер кој спречува бришење на сметка доколку таа има поврзани записи во `transaction_breakdown`. Ова е важно затоа што бришење на сметка со постоечка историја може да доведе до губење на финансиски податоци и неточни извештаи.
- '''Одржување на логичка конзистентност:''' Дополнително, системот користи тригер кој автоматски ја брише трансакцијата доколку по бришење на breakdown запис повеќе не постојат поврзани breakdown записи за таа трансакција. Со тоа се спречува постоење на „празни“ трансакции без финансиско значење.
- '''Ограничувања на уникатност:''' Корисничките мејлови се заштитени со `UNIQUE` ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со иста емаил адреса.
- '''Типови на податоци за финансиски вредности:''' Сите монетарни вредности во системот се складираат со типот `NUMERIC(10,2)`. Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка(`FLOAT` или `DOUBLE PRECISION`) во финансиски пресметки.

=== 4. Одржување на базата
- '''Зачувување на структурните промени:''' Секоја промена на структурата на базата се документира во Trac документацијата и се имплементира директно во PostgreSQL преку SQL наредби. На тој начин документацијата останува усогласена со реалната имплементација на системот.
- '''Чување на SQL скриптите:''' Секоја SQL скрипта извршена после првата DDL скрипта ја чувам хронолошки, за во случај да треба базата да се иницијализира повторно, да ги извршам редоследно и да ја добијам истата состојба која сум ја имал.
- '''Одговорност за инфраструктурата:''' FEiN е дизајниран да работи врз PostgreSQL сервер обезбеден од надворешна инфраструктура. Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација и зависат од околината во која е поставена базата на податоци.
https://develop.finki.ukim.mk/projects/FEIN/wiki/SmallDatabasePerformance
Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата.

Во моментот на тестирањето, базата ја има следната количина на податоци:

||= Табела =||= Број на редови =||
|| `user` || 3 ||
|| `transaction` || 12 ||
|| `transaction_account` || 5 ||
|| `transaction_breakdown` || 11 ||
|| `tag` || 11 ||
|| `tag_assigned_to_transaction` || 16 ||

Поради малата количина на податоци, очекувано е PostgreSQL во повеќе случаи да избере `Seq Scan`, бидејќи целосното читање на мала табела е поевтино од пристап преку индекс. Затоа, во анализата не се гледа само времето на извршување, туку и дали индексот навистина се користи во execution plan-от.

==== Предложени дополнителни индекси ====

Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи:

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови.

==== Сценарио 1: Трендови на трошење според тагови за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
tg.tag_name,
DATE_TRUNC('month', t.date) AS month,
SUM(tb.spent_amount) AS total_spent
FROM tag_assigned_to_transaction tat
JOIN "transaction" t
ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb
ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg
ON tat.tag_id = tg.tag_id
WHERE
ta.user_id = 1
AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY
tg.tag_name, month
ORDER BY
tg.tag_name, month;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

'''План пред додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.176..0.189 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=5
->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.162..0.167 rows=16 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 25kB
->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.119..0.142 rows=16 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.086..0.099 rows=16 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.057..0.066 rows=16 loops=1)
Hash Cond: (tat.transaction_id = tb.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
->  Hash
->  Seq Scan on transaction t
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 0.733 ms
Execution Time: 0.296 ms
}}}

'''План по додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.154..0.166 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=5
->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.142..0.146 rows=16 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 25kB
->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1)
Hash Cond: (tat.transaction_id = tb.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
->  Hash
->  Seq Scan on transaction t
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 0.921 ms
Execution Time: 0.242 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.733 ms || 0.921 ms ||
|| Execution Time || 0.296 ms || 0.242 ms ||
|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
|| Дали новите индекси се користат || Не || Не ||

'''Заклучок:'''
Времето на извршување се намали од 0.296 ms на 0.242 ms, што претставува намалување од приближно 18.2%. Сепак, execution plan-от и по додавање на индексите користи `Seq Scan` над сите релевантни табели, а не `Index Scan` или `Bitmap Index Scan`. Затоа подобрувањето не може да се припише директно на индексите. Најверојатната причина е малата количина на податоци и фактот што сите блокови се веќе во shared buffers. Индексите остануваат логични за идно зголемување на бројот на трансакции, тагови и breakdown записи.

==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ====

'''Цел:'''
Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
COALESCE(SUM(tb.spent_amount), 0) AS total_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
total_spent DESC
LIMIT 10;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''План пред додавање на индексите:'''

{{{#!sql
Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.110..0.114 rows=3 loops=1)
Buffers: shared hit=3
->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.108..0.111 rows=3 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: quicksort  Memory: 25kB
->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.097..0.101 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.059..0.069 rows=11 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.032..0.038 rows=11 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.403 ms
Execution Time: 0.191 ms
}}}

'''План по додавање на индексите:'''

{{{#!sql
Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1)
Buffers: shared hit=3
->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: quicksort  Memory: 25kB
->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.086..0.090 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.060..0.070 rows=11 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.030..0.036 rows=11 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.451 ms
Execution Time: 0.166 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.403 ms || 0.451 ms ||
|| Execution Time || 0.191 ms || 0.166 ms ||
|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
|| Дали новите индекси се користат || Не || Не ||

'''Заклучок:'''
Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со `Seq Scan` на `transaction`, `transaction_breakdown` и `transaction_account`, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на `transaction.date` сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период.

==== Сценарио 3: Годишни трендови на трансакции за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`.

Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
ta.user_id = 1
AND t.date >= DATE_TRUNC('year', CURRENT_DATE)
AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
ta.account_name;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''План пред додавање на индексите:'''

{{{#!sql
Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.141..0.144 rows=3 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 25kB
Buffers: shared hit=3
->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1)
Hash Cond: (t.transaction_id = tb.transaction_id)
->  Seq Scan on transaction t
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
Planning Time: 0.402 ms
Execution Time: 0.242 ms
}}}

'''План по додавање на индексите:'''

{{{#!sql
Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 25kB
Buffers: shared hit=3
->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1)
Hash Cond: (t.transaction_id = tb.transaction_id)
->  Seq Scan on transaction t
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
Planning Time: 0.500 ms
Execution Time: 0.212 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.402 ms || 0.500 ms ||
|| Execution Time || 0.242 ms || 0.212 ms ||
|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
|| Дали новите индекси се користат || Не || Не ||

'''Заклучок:'''
Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи `Seq Scan`, а не новите индекси. Причината е малата количина на податоци: `transaction` има 12 редови, `transaction_breakdown` има 11 редови, а `transaction_account` има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по `user_id` и по временски период.

==== Финален заклучок од анализата ====

Во сите три сценарија, PostgreSQL продолжи да користи `Seq Scan` и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan.

Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс.

Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува `Index Scan`, `Bitmap Index Scan` или `Index Only Scan`. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување.

Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и breakdown записи, индексите на `transaction.date`, `transaction_account.user_id`, `transaction_breakdown.transaction_id`, `transaction_breakdown.transaction_account_id`, `tag_assigned_to_transaction.transaction_id` и `tag_assigned_to_transaction.tag_id` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи.



